From John McGimpsey...................
Using circular references and worksheet functions
You can use a circular reference to enter the time when a change is made in
another cell, then maintain that time. Choose Tools/Options/Calculation
(Preferences/Calculation for Macs) and check the Iteration checkbox. Then, if
your target cell is A1 and you want the date/time to appear in B1, enter this in
B1:
=IF(A1="","",IF(B1="",NOW(),B1))
Format B1 as you wish to display date, time, or both. If A1 is initially blank,
B1 will return a null string (""). When a value is entered into A1, B1 will
evaluate as "", therefore NOW() will be returned. After that (as long as A1
remains populated), B1 will evaluate to a date/time and therefore will return
the value in B1 - i.e., the date/time.
.................................................. .
Gord Dibben MS Excel MVP
On Sun, 6 Apr 2008 06:55:54 -0700 (PDT),
wrote:
I'm trying to develop a worksheet using Mobile Excel - and among that
version of Excel's 'virtues' is that it doesn't do / support / allow
macros.
Therefore, I'm reduced to trying to come up with a cell formula that
evaluates to a timestamp (date and time) when another cell gets a
value put into it - ant that timestamp doesn't change.
This is a piece of cake if you can use macros, but I have yet to come
up with a way to do this that doesn't recalculate / update all the
timestamp formulas when a recalculation takes place (I'm testing that
by using F9).
Has anyone ever cracked this nut? If the explanation isn't clear,
please ask questions and I'll elaborate...
Thanks!
James