Circular Reference when opening.

Brass Contributor

 

When opening my excel file, I get a circular reference Warning.

 

I click "OK" and it finishes opening. I go to "Formulas/Error Checking" and circular references is greyed out indicating there are none.

 

I only get the warning when opening because it detected the circular reference before it finished loading all the data but when all the data is loaded, it solves the issue. 

 

This doesn't affect anything it's just a major annoyance. Is there any way to fix this without turning of error checking?

 

Thank you

7 Replies

@Immortalis  wrote:  ``I only get the warning when opening because it detected the circular reference before it finished loading all the data but when all the data is loaded, it solves the issue. ``

 

That is conjecture, and it is not a logical one.

 

There are many other possibilities.  But you provide insufficient detail for a reasonable attempt to triage the problem.

 

First, after observing that circ ref error checking is greyed out, what is the state of the Iterative Calculation option?

 

Second, what happens if you open the file in "safe mode"?

 

To accomplish that, press and hold Ctrl, and continue pressing Ctrl while you click on an Excel program icon (not a file icon).  Continue pressing Ctrl until you see the desktop pop-up to confirm "safe mode".  Confirm "safe mode".  When Excel opens, you should see "safe mode" in the window title.

 

In that "safe mode" instance of Excel, open the file.

 

Questions:

(a) Do you get the circular reference error?

(b) Is the circ ref error checking option still greyed out?

(c) If not, are you able to locate the circ ref?

 

Finally, if none of that is productive, can you attach an example Excel file that demonstrates the problem?

 

Click the "browse" link at the bottom of the reply applet.  If the forum does not permit that (yet), upload the file to a file-sharing website, and post the download URL in a response here.  I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.  If the form does not permit you to paste the URL (yet), manually edit it so it does not look like a URL.  For example, the "URL" for this thred is techcommunity dot microsoft dot com /t5/excel/circular-reference-when-opening/m-p/3622888 .

 

Of course, you should redact the file (i.e. remove revealing private information like names and addresses).  And ideally, reduce the file to a minimum, as best as reasonably possible.

 

If none of that pans out, unfortunately I have only scratched the surface of arm's-length questions that we could ask.

 

@Joe User 

 

Thank you for responding.

 

Iterative Calculation is not enabled.

 

In Safe mode, the warning still appears and Circular References option is still greyed out.

 

I will attach the file.

 

Thank you very much.

@Immortalis 

 

Thanks for the file.

 

I'm afraid that this might get very complicated. I hope nothing is lost in the confusion.

 

Caveat: I use Excel 2010. So some of the behavior that I see might differ due to opening a file with a different version. Also, the navigation to some features might differ.

 

-----

 

I __am__ able to duplicate the unexpected behavior that you describe.

 

That is, when I open your file and enable macros, I get a circ ref warning, but circ ref error checking is greyed out, suggesting that there is no circ ref.

 

However, when I open your file and disable macros, circ ref error checking identifies Aug!K36 and Aug!AQ36.

 

JoeUser_0-1662922133429.png

 

Also note the circ ref cell identified in the lower left of the status bar: AM156 in the Sep worksheet.

 

JoeUser_1-1662922243425.png

 

I don't know why that does not appear in the circ ref error checking list. Perhaps it is part of the chain of calculations that "starts with" either Aug!K36 or Aug!AQ36.

 

In fact, if you look at each worksheet, you will see additional "local" circ refs in the lower left of the status bar.

 

But beware of bogus local "circ refs". For example, BaseInfo!B3 contains the formula =TODAY(). Of course, that cannot be a circ ref.

 

I suspect that B3 is simply the last cell that Excel calculated on that worksheet when it decided that there might be a circular chain of calculations.

 

My guess is.... That is simply based on an arbitrary number of calculated cells in the chain.

 

-----

 

To disable macros, I set an option in Trust Center > Trust Center Settings > Macro Settings. I always choose "disable...with notification".

 

Then, after loading, I click "x" on the right of the notification instead of clicking "Enable" on the left.

 

JoeUser_2-1662922359231.png

 

-----

 

Explanation (speculation)....

 

I note that the file was saved with Automatic Calculation enable, and there are several "volatile" VBA functions.

 

My guess is.... Initially, the file has circ refs, and that is what Excel reports.

 

But after recalculation due to the volatile functions when the file is opened, conditions change such that the circ refs are "masked".

 

To explain.... Consider the following formula in A1 in a new workbook:
=IF(B1, A1+1, 0).

 

Initially, there is no circ ref error and A1 displays 0 because B1 (empty) is interpreted as FALSE (0), so the value-if-true expression (A1+1) is ignored.

 

But when we enter TRUE into B1, then we get a circ ref because the circ ref A1+1 is "unmasked" by the change in the logic.

 

You have many complicated IF formulas, and the volatile VBA functions might alter their behavior.

 

-----

 

That is as far as I can take this, because of the unduly complex nature of the IF formulas.

 

Hopefully, your intimate knowledge of what you are trying to do will help you isolate the circ refs.

 

Unfortunately, I don't believe that Excel provides much assistance.

 

You might try using Evaluate Formula (not available in Excel for Mac). But beware that sometimes EF does not exactly mimic normal formula evaluation.

 

Good luck!

 

-----

 

BTW, I believe you have a defect in one VBA function -- and perhaps that is causing the unexpected change in circ ref conditions.

 

Function MyDatePS(RCell As Range)
MyDate = Date
End Function

 

should be

 

Function MyDatePS(RCell As Range)
MyDatePS = Date
End Function

 

You should use Option Explicit at the top of each VBA module. That might expose such typos.

 

Thank you VERY much for your time and diagnosis. I realize it's a very complicated file with many references to other references and other sheets and contains many formulas.

I've been working on it for 5 years and making changes as I go, as needed, as well as constantly trying to optimize it and avoid volatility wherever possible. It's quite possible that the problem lies in an area where formula or structure has been changed over from a previous idea to a new one.

My knowledge of Excel is... somewhat of an advanced level of basic. Lol.

You've definitely helped immensely though in finding where problems lie as well as how to detect them. The VBA error was something I had tried out at one time but couldn't get it to work how I wanted. I don't use it at all and as such, have removed the module now.

I'll take the information you've given me and see if I can fix the errors. It might take a few days due to lack of time but I'll update you on the results.

Once again, Thank you so much, Sir!

@Immortalis  wrote:  ``I'll take the information you've given me and see if I can fix the errors. It might take a few days due to lack of time but I'll update you on the results.``

 

I would appreciate that.

@Joe User 

 

I finally got around to working on the problem. Your trace of the "Aug" circular references was the key factor. It indeed was an overlap between old formulas and new ones I had implemented and because everything references everything, fixing one circular reference created another. I had to repair about 10 of them to clear them all and I no longer get the warning when opening my spreadsheet.

 

Thank you so very much! 

@Immortalis  wrote: ``I no longer get the warning when opening my spreadsheet.``

 

Great!  Congrats!