circular reference - cell cannot be listed

susanbond

New Member
Joined
Aug 31, 2004
Messages
3
This has happened to me 5-6 times in the same file, and I can't figure out why:

I make a change (usually involving dragging a formula across several cells). I get an error message saying that the open workbook has a circular reference, but the cell reference that is causing it cannot be listed. I undo all recent changes but the 'circular' message does not go away. I then close the file without saving and open an earlier version, in which I am able to make the SAME change without an error message. However, as I continue to modify cells, the message pops up again, and the cycle repeats.

Does anyone have any suggestions for finding circular references that Excel won't display?
Any help is appreciated!
Susan
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430
Hi Susan,

I am on WindowsXP and I get a message at the bottom of my screen when there is a curcular reference and what cell. If you are on XP look at the bottom and it should tell you.

Have you tried going to TOOLS , FORMULA AUDITING to trace the paths of the formula?

Ron
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Welcome to the Board!

Have you tried displaying the circular reference toolbar? (View, Toolbars, Circular Refernce.) That will enable you to display the dependents and or precedents of any circular references.
 

susanbond

New Member
Joined
Aug 31, 2004
Messages
3
Thanks for the suggestions. Unfortunately Excel will not give me the cell reference that is causing the circular error. Normally it does but this time the error message says "cell cannot be listed" and at the bottom of the screen it just says "circular" with no cell reference. (The Circular Navigation toolbar also does not display a cell reference.) I've tried to audit the error but so far haven't been able to figure it out.

It's as if there is a circular error that is hidden so deeply within the file that Excel doesn't always find it, but when it does, it causes a problem. For example, yesterday I was just copying and pasting numbers (not formulas) and suddenly got the "circular error - cell cannot be listed" message in the middle of doing that. Excel then shut down immediately after. When I reopened the file, it displayed "ready" in the status bar. I tried copying and pasting the SAME numbers, and it worked. Excel calculated and displayed "ready". I was able to save and close, but I think that when I go back today to make other changes the error will appear again.

Has this happened to anyone else before? Thanks for any help!

Susan
 

Forum statistics

Threads
1,148,053
Messages
5,744,534
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top