How do I have a cell update itself only if it's currently blank?

[reservations]

I need to track to retain the first value of a date field. Each time
I load the worksheet the date field has the potential to change, I
need to keep track of the initial date in addition to the current date
that is in the field.

I have date columns called ASSIGNED and 1st ASSIGNED. The initial
value of both will be blank. At some point the ASSIGNED will be
populated with a date. When it is populated for the first time I want
to save this value in the 1st ASSIGNED column.

I need something like:
IF ( ISBLANK(ASSIGNED), "", ASSIGNED )
in my 1st Assigned cell, however, I do not want every value of
ASSIGNED. I only want to update 1st Assigned if 1st Assigned does not
yet have a value. I can't figure out how to put this kind of logic
together without creating a circular reference.

Can someone help?

 

[Chip Pearson]

without creating a circular reference.

Go ahead and use a circular reference. Used properly and carefully, they can
be quite useful. On the Tools menu, choose Options, then the Calculation
tab. There, check the Iteration box and set Maximum Iterations to 1. Then
use a formula like the following in B1.

=IF(A1="",B1,IF(B1=0,A1,B1))

See http://www.cpearson.com/Newsletter/Content/2007_07_23.htm for more about
circular references.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)