Formula using date and time to generate unique ID

Hello -

I am looking for a way to generate a unique ID in A1 using the date and time B1 had data input in a YYMMDDhhmmss format. I can't use the NOW() formula as that updates every time Excel is opened. Once the number is generated, I need it to stay the same. Does anyone have any suggestions on how to do this?

Thanks!

You'll need a VBA Change Event Code for that...

something like this
Right click on the Sheet's Tab - View code
Paste the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range, c As Range
Set myrange = Intersect(Target, Range("B:B"))
If Not myrange Is Nothing Then
    For Each c In myrange
        With Cells(c.Row, "A")
            .Value = Format(Now, "YYMMDDhhmmss")
            .NumberFormat = "0"
        End With
    Next c
End If
End Sub

Thank you for the suggestions! I tried the VBA code Johnmo1 shared. The number is changing every time B1 is changed. Is there a way to make this a static number?

OK, so it needs to stay the same even if B1 is changed AGAIN later ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range, c As Range
Set myrange = Intersect(Target, Range("B:B"))
If Not myrange Is Nothing Then
    For Each c In myrange
        With Cells(c.Row, "A")
            If .Value = "" Then
                .Value = Format(Now, "YYMMDDhhmmss")
                .NumberFormat = "0"
            End If
        End With
    Next c
End If
End Sub

Originally Posted by Jonmo1

OK, so it needs to stay the same even if B1 is changed AGAIN later ?

Yes. I'm essentially creating a log with the YYMMDDhhmmss as a ticket number.

Does anyone have any more suggestions on how to make it a static entry with VBA?

Originally Posted by penny

Does anyone have any more suggestions on how to make it a static entry with VBA?

Did you try my last suggestion from post #5

Originally Posted by Jonmo1

Did you try my last suggestion from post #5

My apologies! I think I mistyped the first time I tried it. It works great now, thank you!

You're welcome.