View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
MyVeryOwnSelf[_2_] MyVeryOwnSelf[_2_] is offline
external usenet poster
 
Posts: 143
Default How do I stop my formulas from updating?

How do I stop my formula =IF(D81,NOW(),"") from updating every
time I open the document? I am using this formula for a form that is
filled out electronically; I am using this formula with a locked cell
so that the date and time can't be modified. If I save the document
and then reopen it refreshes every time I do. How can I stop this from
happening?


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

Then, if your formula is for A1, try something like this as the formula:
=IF(D81,IF(N(A1)0,A1,NOW()),"")

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.