Circular reference error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda - 26 Apr 2004 22:20 GMT

Hi Guys,

Using Excel 2000. I have a workbook that is already saved. When I open the
workbook I get this error.

MS Excel cannot calculate a formula. There is a circular reference in an
open workbook, but the references that cause it cannot be listed for you.
Try editing the last formula you entered or removing it with the Undo
command.

Problem is, I don't know what I did last. There are about 8 sheets.

How do I figure out which formula is a circular reference? I don't even
know what that is, I am pretty much a beginner in excel. My formulas are
not really formulas, they are just really copying totals from one sheet and
adding showing the totals on another. There is a sheet that adds totals
from 2 sheets.

Thanks,
Linda

Reply to this Message

mudraker - 27 Apr 2004 00:29 GMT

a circular reference refers to a formular or a group of formulars tha
tries to calculate using the cell that the formular is in

eg of a circular formula in a10

in cell a10 you have a formula =sum(a1:a10)

another example of how 2 formulas can cause a circular reference

sheet1 cell a1 has formula =Sheet2!A1
sheet2 cell a1 has formula =Sheet1!A1

As I don't use excel 2000 i can not advise you of the best method t
trace your circular reference

The following is from Excel 97 Help

Locate cells that create a circular reference

Microsoft Excel cannot automatically calculate all open workbooks whe
one of them contains a circular reference, a formula that directly o
indirectly refers back to the cell that contains the formula.
To locate the cells in the circular reference, use the Circula
Reference toolbar. The toolbar helps you move to each cell in th
circular reference so that you can change the formulas as needed t
correct the circular reference. Microsoft Excel displays the Circula
Reference toolbar automatically when a circular reference occurs.

1    On the Circular Reference toolbar, click the first cell in th
Navigate Circular Reference box.
2    Review the formula in the cell. If you cannot determine whether th
cell is the cause of the circular reference, click the next cell in th
Navigate Circular Reference box.
3    Continue to review and correct the circular reference until th
status bar no longer displays the word "Circular."

Tips

?    To display the Circular Reference toolbar manually, click Customiz
on the Tools menu, click the Toolbars tab, and then select the Circula
Reference check box.
?    The status bar displays the word "Circular," followed by a referenc
to one of the cells contained in the circular reference. If the wor
"Circular" appears without a cell reference, the active worksheet doe
not contain the circular reference.
?    When the Circular Reference toolbar is displayed, tracer arrows ar
displayed to point out each cell referenced in the formula and th
cells that depend on the formula.

?    You can move between cells in a circular reference by double-clickin
the tracer arrows.
?    For more information about circular references in formulas, click

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Linda - 27 Apr 2004 03:04 GMT

Thanks....I looked at that toolbar and didn't really understand it and I was
hoping there is an easier way. I'll keep fiddling with it and hope someone
has a simple idea for me. <g>

Linda

> a circular reference refers to a formular or a group of formulars that
> tries to calculate using the cell that the formular is in
[quoted text clipped - 51 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Linda - 27 Apr 2004 04:09 GMT

I noticed that I left the workbook on a certain sheet. When I opened it up,
a different sheet was open. I looked thoroughly through the sheet and found
the errors, it was obvious by having a #ref in the bad cells instead of
data. So....it appears excel helps you by at least opening up to the
disabled sheet and by having the #ref in the cells.

Thanks for explaining a circular reference, I actually understand it when I
am reading your explanation!

Linda

Reply to this Message