How to resolve a circular reference error that does not exist?

Hi Ivana,

I had a look to your workbook and in my opinion, the formula in C28 has no circular reference but the workbook contains several circular references that you have to correct.

When you get the circular reference warning, click OK and look in the statusbar to see where Excel has detected a circular reference, e.g. circular references: G74.

HTH

Wkr,

JP Ronse

Ivana wrote:

Here's a link. Sorry for not being clearer in my original post. C28 is highlighted. It is an array, but I didn't hit ctrl-shift-enter to demonstrate that that creates a circular reference error even though there seem to be no references, let alone circular references. The rest of the cells even the similar ones all around it don't seem to have a problem. Just seems to be in C28. Hit ctrl-shift-enter yourself to see my problem. Thanks for your help.

https://skydrive.live.com/redir?resid=2A94FF4F18EDB80F!112&authkey=!AB-Y0YM8JNpHLOU

First, yes, when I array-enter the formula in C28, I get a pop-up about a circular reference error.  More importantly, it says that Excel cannot list the references; also, the Circular Reference option in the Error Checking menu on the toolbar is greyed out, as you mentioned in a related thread.  All of these tell us that Excel is very confused; that is, the circular reference is very complicated.  As further evidence of this, keep an eye on the lower-left part of the status bar as you change the values in C20:C24 from 2 to 1 to 2.  Excel indicates different cells as "having" (being involved in) a circular reference.

So do not expect to simply look at a formula and identify the circular reference directly or even within one or two level of references.  Our inability to see the circular reference does not mean it is not there.  It just means the circular chain of references is much longer than we (or even Excel) can follow easily.

Second, the reason why the circular reference might depend on the constant values in C20:C24 is (my guess) that the circular reference arises from a condition of an IF expression or similar conditional function (e.g. CHOOSE).  When we write IF(condition,expression1,expression2), Excel evaluates expression1 only if the condition is true, and it evaluates expression2 only if the condition is false.

So one of the IF() formulas is part of the circular chain of references.  Unfortunately, that is not too helpful because you have a lot of IF() formulas.  BTW, some of those are not array-entered, as they should be.  But that's a separate issue.

Finally, the real question is:  where do you go from here?

I'm afraid I don't know.  The workbook is very complicated.  I don't know of any tool that will find circular references (perhaps someone else does), since the Excel Circular Reference Error Checking operation is greyed out.

I would start over.  That might sound foreboding, given the eventual complexity of the worksheet.  But then again, many of the formulas are the same form.  So once you get one set of formulas working, it might be a "simple matter" of copying and pasting them.  The key might be to correctly array-enter the formulas that need to be from the get-go.

Good luck!