Hi

Is there a way to generate a fixed serial number made up from the date and
time when an adjacent cell is given a value?

STATUS REF
R1 | Open | ?????????? |

I want to give each row entry a unique serial reference number which can
then be referenced and matched to identical entries in other worksheets. But
I only want this to be generated when text is enetered in the cell before it.
Once generated, I don't want the serial number to change with the date.

Any ideas would be appreciated.

Simon

Hi Pieman

You can use the change event in the sheet module to add the serial number
This example will run when you change a cell in A1:A20 and add the date/time in B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pieman" wrote in message ...
Hi

Is there a way to generate a fixed serial number made up from the date and
time when an adjacent cell is given a value?

STATUS REF
R1 | Open | ?????????? |

I want to give each row entry a unique serial reference number which can
then be referenced and matched to identical entries in other worksheets. But
I only want this to be generated when text is enetered in the cell before it.
Once generated, I don't want the serial number to change with the date.

Any ideas would be appreciated.

Simon

Excellent, that works perfect. Many thanks for your help.

Simon

"Ron de Bruin" wrote:

Hi Pieman

You can use the change event in the sheet module to add the serial number
This example will run when you change a cell in A1:A20 and add the date/time in B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pieman" wrote in message ...
Hi

Is there a way to generate a fixed serial number made up from the date and
time when an adjacent cell is given a value?

STATUS REF
R1 | Open | ?????????? |

I want to give each row entry a unique serial reference number which can
then be referenced and matched to identical entries in other worksheets. But
I only want this to be generated when text is enetered in the cell before it.
Once generated, I don't want the serial number to change with the date.

Any ideas would be appreciated.

Simon

Hi again, just noticed that the code you suggested changes each time the
selection in column A1 is changed. Is it possible to fix the date number, so
if the entry in column A1 is updated the date number remains the same?

Thanks
Simon

"Ron de Bruin" wrote:

Hi Pieman

You can use the change event in the sheet module to add the serial number
This example will run when you change a cell in A1:A20 and add the date/time in B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pieman" wrote in message ...
Hi

Is there a way to generate a fixed serial number made up from the date and
time when an adjacent cell is given a value?

STATUS REF
R1 | Open | ?????????? |

I want to give each row entry a unique serial reference number which can
then be referenced and matched to identical entries in other worksheets. But
I only want this to be generated when text is enetered in the cell before it.
Once generated, I don't want the serial number to change with the date.

Any ideas would be appreciated.

Simon

Hi Pieman

If you only want to add the date/time the first time you change the cell in A

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
If Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pieman" wrote in message ...
Hi again, just noticed that the code you suggested changes each time the
selection in column A1 is changed. Is it possible to fix the date number, so
if the entry in column A1 is updated the date number remains the same?

Thanks
Simon

"Ron de Bruin" wrote:

Hi Pieman

You can use the change event in the sheet module to add the serial number
This example will run when you change a cell in A1:A20 and add the date/time in B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Pieman" wrote in message ...
Hi

Is there a way to generate a fixed serial number made up from the date and
time when an adjacent cell is given a value?

STATUS REF
R1 | Open | ?????????? |

I want to give each row entry a unique serial reference number which can
then be referenced and matched to identical entries in other worksheets. But
I only want this to be generated when text is enetered in the cell before it.
Once generated, I don't want the serial number to change with the date.

Any ideas would be appreciated.

Simon