Thread: NOW () formula that won't change next day !

Attached a screen shot.

Column H has a drop down for F / P

If F selected column K reads Fail

If P selected, column K reads Today's date.
Formula = =IF(H14= "P", NOW(), IF(H14= "F", "Fail", IF(H14="-","No Run")))

However, if I open the sheet tomorrow, that date changes !

How can I get it to read the date when P is selected, and keep it that way any time I re open the file?

Creating a Macro is way too much work. Not that I know how to do that.

So no other way around it i guess !

There is no other way. Right click on the sheet tab and choose "View Code". Paste this code into the text box in VBA Editor

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H12:H100")) Is Nothing Then Exit Sub
If Target = "P" Then Target.Offset(0, 3).Value = Now()
If Target = "F" Then Target.Offset(0, 3).Value = "Fail"


End Sub

Close VBA Editor

Does that work for you?

Thanks a million.

This works, I tested by changing the machine time and date.

Can you be a sport and guide me to a site where I can learn more of VB for excel please !

http://www.excelforum.com/excel-gene...additions.html
Chalk full of good links. VBA links are at the bottom of the first post.

One more thing. I found out if I insert a new Column, the formula is not in effect since it is pointing to H
is there a way to make dynamic ?

Create a range name for H12:H100 (select the cells, then type a range name into the name box in the upper left corner, left of the formula bar), for example MyRange. Then change the code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("MyRange")) Is Nothing Then Exit Sub
If Target = "P" Then Target.Offset(0, 3).Value = Now()
If Target = "F" Then Target.Offset(0, 3).Value = "Fail"

End Sub