static today date function based on 2 criteria

[Beatrix]

Hi All ,

I use below formula to update the date based on Yes/No criteria. However I need a static date. When I use Y/N in A5 then F5 should reflect the date data entry is done and stay the same. Is there any way I could get it done by a formula or any other ideas?

=IF(OR((A5="Y"),(A5="N")),TODAY(),"")

Cheers
B.

[Beatrix]

I found this script on another forum and wanted to share for those who need a similar solution I had. It creates today date as static based on the value entered in a specific column.





Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A2:A100")) Is Nothing Then

With Target(1, 6)

.Value = Date

.EntireColumn.AutoFit

End With

End If

End Sub

[SamT]

With Target(1, 6)
What is the function/purpose/meaning of the (1, 6)?

Never mind, I see it is shorthand for Target.Offset(0, 5)

[Beatrix]

yes it is offset function.

[SamT]

Not exactly

Target.Offset(-1, -5) works
Target(-1, -6) fails

Still, it's a good shorthand to be aware of.

[Beatrix]

sure you know better than me I'm a newbie. Thanks for the explanation.