Excel 2k

  • Thread starter Thread starter Timothy O'Hara
  • Start date Start date
T

Timothy O'Hara

My office has a 5 workstation LAN and we're sharing files in Excel 2000. I
am occasionally getting this dialog when I open an Excel file:

Microsoft excel cannot calculate a formula. Cell references in the formula
refer to the formula's result, creating a circular reference. Try one of the
following:

* If you accidentially created the circular reference, click OK. This will
display the Cricular Reference toolbar and help for using it to correct your
formula.
*For more inofrmation about circular references and how to work with them,
click Help.
* To continue leaving the formula as it is, click Cancel.


OK Cancel Help


If I click the "OK" and close the help dialogue and go to Tools, Options,
Calculations and check the "Iteration" check box the problem disappears
until some elese opens the file.

Its annoying more than anything else since the file can pass between 2-3
people before its used in a document. Any suggestions on how to make this
"iteration" check box stay checked?

Thanks,

Tim O'Hara
 
Timothy,

You have a self-referential formula somewhere in a worksheet. You need to
determine if it was really intended fo have such a formula (in which case
you would use the Iteration option). Iteration will get rid of the error,
but may produce incorrect results if it wasn't intended in the design of the
worksheet. Such a formula "chases its tail," must be used carefully, and is
not usually used in serious work.
 
More adding.

You asked about the Iteration checkbox not staying checked.

This is similar to the problem with Calculation Mode.

The settings for the first workbook opened sets the default for subsequent
workbooks.

If you want to leave the Circular Reference in place and Iterations checked,
this workbook must be opened first.

I would follow Dave's and Earl's suggestion that you find and remove the
Circular Reference.


Gord Dibben Excel MVP
 
Back
Top