Phantom Circular

jpf5911

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

pgc01

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

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,375
Members
410,679
Latest member
rolandbianco
Top