A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Circular reference error



 
 
Thread Tools Display Modes
  #1  
Old April 26th, 2004, 10:20 PM
Linda
external usenet poster
 
Posts: n/a
Default Circular reference error

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


  #2  
Old April 27th, 2004, 12:29 AM
mudraker
external usenet poster
 
Posts: n/a
Default Circular reference error

a circular reference refers to a formular or a group of formulars that
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 to
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 when
one of them contains a circular reference, a formula that directly or
indirectly refers back to the cell that contains the formula.
To locate the cells in the circular reference, use the Circular
Reference toolbar. The toolbar helps you move to each cell in the
circular reference so that you can change the formulas as needed to
correct the circular reference. Microsoft Excel displays the Circular
Reference toolbar automatically when a circular reference occurs.

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

Tips

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

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


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

  #3  
Old April 27th, 2004, 03:04 AM
Linda
external usenet poster
 
Posts: n/a
Default Circular reference error

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

"mudraker " wrote in message
...
a circular reference refers to a formular or a group of formulars that
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 to
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 when
one of them contains a circular reference, a formula that directly or
indirectly refers back to the cell that contains the formula.
To locate the cells in the circular reference, use the Circular
Reference toolbar. The toolbar helps you move to each cell in the
circular reference so that you can change the formulas as needed to
correct the circular reference. Microsoft Excel displays the Circular
Reference toolbar automatically when a circular reference occurs.

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

Tips

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

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


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



  #4  
Old April 27th, 2004, 04:09 AM
Linda
external usenet poster
 
Posts: n/a
Default Circular reference error

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


"mudraker " wrote in message
...
a circular reference refers to a formular or a group of formulars that
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 to
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 when
one of them contains a circular reference, a formula that directly or
indirectly refers back to the cell that contains the formula.
To locate the cells in the circular reference, use the Circular
Reference toolbar. The toolbar helps you move to each cell in the
circular reference so that you can change the formulas as needed to
correct the circular reference. Microsoft Excel displays the Circular
Reference toolbar automatically when a circular reference occurs.

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

Tips

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

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


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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.