Question re: TODAY or NOW functions

[Voutsy]

Hi,
I use MS Office XP, and have set up a BOOK that has 2 worksheets,
worksheet1 named 'checklist' contains a list of 60 establishments wh
send data to me on a monthly basis (rows), with a column for each mont
of the financial year. When i receive data for an establishment i tic
the corresponding cell in 'checklist' with a tick (capital 'P
webdings2).
I have recently set up worksheet2 as 'Date Received' and have copie
the table from checklist here. I want to make it so that as i enter
tick in the checklist, 'date received' will automatically fill in th
date the data was input. I have tried using the following formula:

=IF('Check List'!E3="P",TODAY(),"")

this sort of works, but as a 'volatile' fucntion it will only ever sho
TODAYS date, not the date that the data was input!
I was wondering if anyone knows of a way to make an formula to show th
date that data was entered into a cell without it being a volatil
function.

I would be very grateful for any help with this

 

[MattShoreson]

Why dont you just type in the date when the item is received o
worksheet1 in place of a tick

 

[Don Guillett]

You would have to change the formula to a value or use a macro

=IF ucase(sheets("'Check List'").range("E3"))="P" then activecell=date

 

[Gord Dibben]

Voutsy

CRTL + ;(semi-colon) will enter a hard date. Instead of a tick, enter the
date.

If you truly want it automated you will have to use worksheet event code.

See J.E. McGimpsey's site for worksheet event code to set a date stamp.

http://www.mcgimpsey.com/excel/timestamp.html

Gord Dibben Excel MVP