Hi,
How can I have a timestamp in cell B2 every time cell A2 change value.

I do not want to use vba but rather use a circular reference in Formula.

Many thanks.

Dan

First, goto ToolsOptions and on the Calculation tab check the Iteration
checkbox to stop the Circular Reference message, and use this formula in B2

=IF($A$2="",B2,NOW())

--
__________________________________
HTH

Bob

"Dan" wrote in message
...
Hi,
How can I have a timestamp in cell B2 every time cell A2 change value.

I do not want to use vba but rather use a circular reference in Formula.

Many thanks.

Dan

but then B2 will get a time stamps whenever any cell change - not only a2
because Now() is volatile.


"Bob Phillips" wrote:

First, goto ToolsOptions and on the Calculation tab check the Iteration
checkbox to stop the Circular Reference message, and use this formula in B2

=IF($A$2="",B2,NOW())

--
__________________________________
HTH

Bob

"Dan" wrote in message
...
Hi,
How can I have a timestamp in cell B2 every time cell A2 change value.

I do not want to use vba but rather use a circular reference in Formula.

Many thanks.

Dan

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 Tue, 17 Jun 2008 02:31:01 -0700, Dan wrote:

Hi,
How can I have a timestamp in cell B2 every time cell A2 change value.

I do not want to use vba but rather use a circular reference in Formula.

Many thanks.

Dan