Circular References Error Message Started Popping Up

[JeffGrant]

Hi All, for no apparent reason, excel has started throwing up a circular reference message

I can not find any circular references. The Formulas-> Error Checking -> Circular References is greyed out on all sheets. I have done an Open & Repair and changed to a new copy of the workbook. I use M-Z Tools to clean my vba code.

Any Ideas?

Thank in advance for your help.

 

[SanjayGMusafir]

Generally a message is conveyed with some cell address on lower left corner of status bar of excel if you initiate 'Calculate'

But honestly that is just a starting point to find which formula may be causing the circular reference.
And what I can understand is that you don't remember the last formula you created or modified - Mostly that is the first culprit to check for...
If you remember, try to remove that formula and then re-initiate 'Calculate' to test if things have worked for you or not.

Open and repair thing, to best of my knowledge can't remove/break circular references, as most of them are in the formulae which we populate.

 

[Alex Blakenburg]

You could give the macro posted here a try:

Find circular references within your workbook

To use this macro, run the FindCircRefs macro from the Microsoft Excel worksheet for which you want to find circular references. A new sheet is added to the active workbook, listing the cell addres…

www.exceltips.nl

 

[SanjayGMusafir]

Hi @Alex Blakenburg
I tried using the VBA on the link you mentioned. For some reason my file went to some endless journey and I have to FORCE Stop Excel.

Just for your understanding my file had no Circular Reference. I just thought of giving it a try...

Any particular reason for this thing happening or does it actually takes so long checking?

Thanks a lot

 

[Alex Blakenburg]

Hi @Alex Blakenburg
I tried using the VBA on the link you mentioned. For some reason my file went to some endless journey and I have to FORCE Stop Excel.

Just for your understanding my file had no Circular Reference. I just thought of giving it a try...

Any particular reason for this thing happening or does it actually takes so long checking?

Thanks a lot

I must admit I have not tried it on a larger data set. It does read every cell in the used range of the sheet and does an intersect between that cell and the precedents of that cell so I can imagine that it would not be quick.

 

[SanjayGMusafir]

Thanks @Alex Blakenburg

 

[Alex Blakenburg]

If you have access to the excel enterprise edition through your employer, you will most likely have access to the COM Add-In Inquire, you just need to turn it on.

It should be able to identify all the circular references through its analyse function

Auditing Worksheets with Inquire

I have to compare two versions of a workbook to find what changed.

www.mrexcel.com

 

[JeffGrant]

Hi All, thanks for all your suggestions. But today..No circular reference message popping up. An odd one for sure.

 

[Alex Blakenburg]

I haven't tested it but did you perhaps have another workbook open that did have circular references ?
Also you didn't change the iteration setting did you ?