Explain Circular References

[Arcadian]

I'm trying to enter a 3-D cell reference but keep getting an error
message, saying that I'm creating a circular reference. I don't
understand what this means. I look at their explanation & options but
they're in the language of nuclear physicists, so I have no clue. I
looked it up on the MS site but got such skimpy info, which has
nothing to do with my problem. Where can I get a simple explanation
of what it is and how to remedy it?

Here's my problem exactly: =sum(June:September!B8) -- I'm trying to
total sales figures, in cell B8 of a summary sheet, from the cells B8
in the sheets 'June" to "September". This is based on an exact figure
in an instruction book, which has no mention of circular references.

 

[Dave Thomas]

A circular reference is a formula that has a reference to itself. For
example in cell A1 I put the formula "=A1+6". This is a circular reference.
Where is your formula located? If the summary sheet is included in sheets
June:September, you have a circular reference.

 

[Dave Thomas]

To see another example of a circular reference:

A Results: A

1 6 1 6

2 4 2 4

3 =-a4*0.1 3 -0.9091

4 =sum(a1:a3) 4 9.0909


Notice A3 has a minus sign after the =. This formula is a circular reference
because it depends on the value in A4. But A4 depends on the value in A3.

To allow the Excel to calculate the circular reference.

In Excel 2003 and prior: Tools/options/calculation - check the box
"Iteration"

In Excel 2007: Office Button/Excel Options/Formulas/Calculation Options -
check the box "Enable iterative calculations"

 

[Harlan Grove]

Here's my problem exactly: =sum(June:September!B8) -- I'm trying to
total sales figures, in cell B8 of a summary sheet, from the cells
B8 in the sheets 'June" to "September". This is based on an exact
figure in an instruction book, which has no mention of circular
references.

This could happen if your summary worksheet is between the June and
September worksheets. If that's the case, this formula would include
the cell Summary!B8 in the calculation entered into that same cell.
That's a circular reference: directly or through several stages, the
formula in a cell refers to its own value.

That's probably not what you want. Move the summary worksheet before
the June worksheet or after the September worksheet, and there should
be no more circular reference.