"One Shot" date entry via a formula!

[eddy]

Hi ..

I need a foumula in Cell F67 which will do the following:-
Look at Cell H67, if the contents of Cell H67 = "Complete"
then enter the current date in Cell F67, ELSE leave Cell F67 with
the message "Still Pending"

Dom came up with this solution:-

=IF(H67="Complete",TODAY(),"Still Pending")

But every time the PC date changes so does the date in the cell. That is not what I need. I just need a "OneShot" date entry e.g. enter date that dosn't change when the sheet is re-opened again on a different day.

Using Ctrl + ; gives the result I am looking for

I know this formula dosn't work but it's what I need to do

=IF(H69="Complete", "CTRL+;" ,"Still Pending")

Anyone know the answer.

All help much appreciated.

Ted

 

[whiteghost]

put the formula elsewhere then copy the result and pastespecial values.
the result will no longer be volatile.,

 

[whiteghost]

Pennysaver has some good code for this. see
http://www.mrexcel.com/board2/viewtopic.php?t=127877&start=10

it might be better suited to your needs