change cell event using circular reference - not VBA!
 

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

change cell event using circular reference - not VBA!
 

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

change cell event using circular reference - not VBA!
 

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

change cell event using circular reference - not VBA!
 

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