Phantom Circular

jpf5911

Board Regular
Joined
Jun 10, 2002
Messages
72
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,141,027
Messages
5,703,818
Members
421,318
Latest member
cg_cartoonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top