Mysterious Circular Reference

[rjbjajjjmj]

This is my first time to submit a question here; I hope I'm asking for help correctly. If not, please advise and forgive.

I've encountered something in a co-worker's spreadsheet that has me baffled. A cell contains a simple number (in this case, 12). There are formulas dependent upon that cell. While the value is 12 (or less), there's no hint of a circular reference.

However, when the value in the cell is changed to a value greater than 12, the Circular Reference warning appears. That means there's a funky (probably IF) formula out there that is clean until its result is a function of 13 or more. But what is really troubling me is that, although the Circular Reference warning fires, there is nothing that helps me to locate the problem cell. The traces for dependent or precedent cells do not help. The Formulas tab, Error Checking drop down doesn't indicate a cell address. The left side of the status bar makes reference to no address. Finally, when you do change the value in that cell, clicking OK to proceed causes the file to crash and offers to recover.

This one is new to me. We work in Excel 2010. Any ideas?

 

[JoeMo]

Are there no cells that are dependent on the cell containing the number?

 

[gorem]

Sounds bizarre. I would look for dependencies by doing a CTRL+F for the address of the cell containing the 12 (and make sure to look in formulas). How many different formulas could reference that one number?

 

[Joe4]

Welcome to the Board!
This link will show you how to find where the circular reference exists: Find Circular References in Excel | the MOST

 

[J.Ty.]

Hi,

Welcome to MrExcel.

Look for OFFSET and INDIRECT used in the formulas. I guess they cause the problem.
Additionally, you can use Error checking tool from Formulas menu, it allows for circular reference tracing.


J.Ty.

 

[rjbjajjjmj]

There are. As I mentioned, I think I'll find it in time, but what has me puzzled is that Excel is not identifying a cell where it at least thinks the problem exists.

 

[rjbjajjjmj]

... look for dependencies by doing a CTRL+F

Yes... when I return to the office tomorrow, that would be part of our strategy.

 

[rjbjajjjmj]

Thanks for the link! I'll work on that tomorrow.

 

[rjbjajjjmj]

Excellent suggestion... I hadn't thought about OFFSET and INDIRECT.

 

[rjbjajjjmj]

And thanks to everyone for jumping in on the question. It was a very nice welcome to the site.