circular reference

N

Natasha

Hi! Has anyone used a worksheet with circular references? I'm working on
a spreadsheet which contains quite a lot of circular references. I have
managed to sort some of them out by adjusting the number of iterations
in (tools>options>calculation) but there is a limit of 10000 iterations
and that's not enough. Is there a way to increase this number of
iterations? Thanks. Natasha
 
F

Freemini

Why are you using circular references?

The problem with circular references is they cannot complete the
calculation correctly, hence your sheet will contain errors. The number
of iterations only allows Excel to recalculate the formula, stopping
after the set number (or size of change)

I.E. if iteration is set at 1000
[A1] 1, [B1] 1, [C1] =sum(A1:C1) gives value in C1 as 2000 which will
increment by 2000 every time data is entered.

If your circular references are errors you should edit each occurance.
The cell reference will appear at the bottom of the sheet containing
the error. If the error occurs on a different sheet the message will
read "Circular reference"

Hope this helps

Mike
 
A

Arien

Circular reference has nothing to do with iterations.
It means that formulas refer to eachother. Example:
A1= B1 and B1 = A1.
Use the auditing toolbar to make Circular reference visable.
 
Top