Phantom Circular

[jpf5911]

I have a financial forecast model that occasionally indicates a circular reference even though there is none there.

From time to time, when I open the file, it indicates that there is a circular reference. If I follow the cell and try to find the error, I can never find it. But if I close excel and re-open the file, the circular error reference will not re-appear.

I may use, save and re-open the file repeatedly over a few days with no circular reference, and then one day I'll open it and it says that it's there.

This is a large workbook with some complex formulas. It has about 8 tabs and a lot of references between the tabs. It has only one small section that references another workbook.

Anybody seen this before? I hate circular references.

Thanks,

JPF

 

[pgc01]

Hi JPF

I have a financial forecast model that occasionally indicates a circular reference even though there is none there.

I'd say that if excel indicates a circular reference most probably there is (at least) one.

The problem is that if it's a a large workbook with complex formulas it may not easy to find the origin of the problem.

If it only appears sometimes that's probably because it's inside an IF in a condition that arises very seldom.

Example: write in A1 the formula =IF(B1>3,A1+1). This will only issue an error of circular reference if b takes a value bigger than 3.

Now if you have complex formulas that refer to ranges with complex formulas that refer..., you see the problem of finding the origin. If you close and reopen the workbook it can disappear due to realculation and volatile functions.

So, next time, be patient and go through the precedents tree until you find it.

Hope it helps
PGC