Circular Reference when opening.

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.

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

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.

-----

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.