Lock-in TODAY date?

[pearce_dan]

Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?

I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

Thanks in advance!

 

[pearce_dan]

Hi Oaktree,

1) N5 will never be modified.

2) The date cell will be protected to prevent deletion, which is one reason I also want it to lock in, because to manually input it, I'd have to unprotect the sheet which is a hassle.

 

[gardnertoo]

The circular reference method will work with protected cells, I just tried it.

 

[Oaktree]

With code (right click on your sheet's tab, select "view code", and paste this):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$N$5" Then Exit Sub
If Range("A1") = "" Then
    ActiveSheet.Unprotect "Password goes here"
    Range("A1") = Date
    ActiveSheet.Protect "Password goes here"
End If
End Sub

 

[pearce_dan]

Thanks, I'll let you know how it goes (gotta do it after work).