circular reference - cell cannot be listed

[susanbond]

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

 

[Ron Morris]

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]

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]

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