I have a spreadsheet with drop down menus in cells A9 down to A999. If the menu option "ON HOLD" is selected, then I want to insert todays date into the same row under column W

I know I can use the formula

=IF(A9="On Hold",TODAY(),"")

But if I open the spreadsheet tomorrow the cell with the formula will show tomorrows date, any ideas on how to fix this

You would have to do something along these lines:
http://www.ozgrid.com/VBA/run-macros-change.htm

Looks like Namliam may have beaten me to it but not having followed the link I was going to suggest using the worksheet change event like so

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value = "on hold" Then
Cells(Target.Row, 23).Value = Date
End If
End Sub


Brian

Thanks for the pointer, but I am struggling to get this to work.

Brian thank you this is exactly what I need