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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|