Circular Reference but Excel won't say where it is

  • Thread starter Thread starter Ron K
  • Start date Start date
R

Ron K

I received this message:

"Microsoft Excel cannot calculate a formula. There is a
circular reference in an open workbook but the reference
that caused it can not be found"

Has anyone encountered this?

Thanks
RK
 
Hi

Thanks to Phil Le Grand I was able to find the problem.
Basically if the circular reference is too embedded
throughout the rest of the spreadsheet, then Excel gives
up and will not tell you where the error is. Making my
situation worse was that depending on the inputs to the
model there was not always a circular reference.

If you get this message, try and delete as much of the
spreadsheet as possible (I had 360 months of cash flows
and only left 2). at that point, the spreadsheet may be
small enough for excel to let you know where the problem
is.

This is the response I received from Microsoft after
telling them the problem and solution:


"In general, Excel will show you in which cell the
circular reference occurs in the status bar when you open
the workbook. The reason why Excel can not show on your
end is because that Excel can only track 65,536
dependencies to unique references for automatic
calculation. After the workbook has passed this limit,
Excel no longer attempts to recalculate only changed
cells. Instead, it recalculates all cells after each
change. This behavior is by design of Microsoft Excel.
For more information, please click the link below:

http://support.microsoft.com/default.aspx?scid=KB;EN-
US;243495 "
 
Back
Top