Set a date with dropdown menu in different cell

[feddon]

Hi.
I have a column where the values are "yes" or "no" in each cell. It is "no" as default and i was wondering if there was a way that when changed to "Yes" the cell next to it which contains TODAY() function can be set to the value it is currently at.

I.e

A B
1 No TODAY()


When yes is selected from a drop down list the today is fixed as the date it was:

A B
1 Yes 20/06/2011

i guess it could be done with macros but i can work out how to get it working.
Thanks in advance.

[Blake 7]

Try this

Please Login or Register  to view this content.

i googled "stop =today from updating" loads came up.........

[teylyn]

Hello,

here is a macro that will change the Today() formula to the non-changing absolute date when the cell to the left is changed to Yes.

Please Login or Register  to view this content.

The macro monitors the range A1 to A15 for changes. Adjust to your needs.

Copy the code, then right-click on the sheet tab, select "View Code" and paste the code into the code window. Make the required cell reference changes and close the VBE.

Test it. You will not see an immediate difference, but when you click a cell in column B where A has a Yes, you will see that the formula bar shows the date, not the Today() formula.

cheers,

[feddon]

Thanks for the replys. I tried the second response and it worked a treat.
Thanks again.