Date-time updates every cell?

  • Thread starter Thread starter Penny Miller
  • Start date Start date
P

Penny Miller

I have a worksheet that has a formula in all cells in row H that says if
there is a comment in row G then put the current date/time in H(current
cell). My formula looks like the following in H5;

=IF(ISBLANK(G5),"",NOW())

The problem is that if I enter a comment in any cell in row G it updates all
of my dates in row H.

Using Excel 2007
 
Penny,

For future reference, G and H represent columns; not rows.
The formula contains the NOW() function which returns exactly that each time
the worksheet is recalculated. So each time an entry is made anywhere on the
worksheet and assuming the worksheet is set to automatic calculation, all of
the formulae will display the current time.
To record the time that a particular cell is updated will likely require VBA
code.
Try posting your question in the "Excel-Programming" Group.

Best wishes,
Dave
 
I have a worksheet that has a formula in all cells in row H that says
if there is a comment in row G then put the current date/time in
H(current cell). My formula looks like the following in H5;

=IF(ISBLANK(G5),"",NOW())

The problem is that if I enter a comment in any cell in row G it
updates all of my dates in row H.

Using Excel 2007
Click to expand...

I use Excel 2003, but 2007 might be similar.

One way is to use a circular reference. To allow circular references, use
Tools > Options > Calculation
and check the "Iterations" checkbox.

Then, try something like this in H5:
=IF(G5="","",IF(H5="",NOW(),H5))

Adjust to suit.

Caution: allowing circular references can be a risk. Later, if somebody
adds a circular reference in the workbook by mistake, Excel won't flag it
as an error.
 
Back
Top