PDA

View Full Version : [SOLVED] static today date function based on 2 criteria



Beatrix
09-14-2015, 06:52 AM
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
10-05-2015, 09:35 AM
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
10-05-2015, 06:43 PM
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
10-07-2015, 02:42 AM
yes it is offset function.

SamT
10-07-2015, 07:15 AM
Not exactly

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

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

Beatrix
10-07-2015, 07:33 AM
sure you know better than me I'm a newbie. Thanks for the explanation.