Excel: Circular Reference Mystery/Oddity

Factual Questions
#1

A couple of years ago I developed a certain model in Excel. The model did not have any circular references in it - I would definitively have noticed it (among other things, Excel gives you a warning every time you open a file with a CR), and I also sent it for peer review by other actuaries before we began using it.

Of late people have been sending me for review models they’ve used that contain circular reference warnings. These models look identical to the original versions that I have which don’t have these warnings.

If you go to one tab in the spreadsheet it identifies specific cells that have circular references (one at a time - if you delete one cell another shows up as the problem) but if you delete that entire tab the error message remains, though no longer identifying a specific cell.

Furthermore, if you open the problematic models and then open the original versions of the model, then they too begin showing the same CR warning, and this persists even if you close the problematic model. But you can then save the other model (with warning still showing) and immediately reopen it, and the warning does not appear. If you have the problematic models open and open another Excel file (meaning not another copy of the model) then the CR warning appears in that file but goes away if you close the model.

What could possibly be going on? (FWIW, I believe the CR versions of the model are all coming from the same office, which has had other problems with their file system - specifically involving Word documents linked to Excel files.)

#2

If you’ve truly got a circular reference, then deleting one of the cells in the circle will always cause a problem, because it’ll deny some other cell the reference it’s supposed to have. Presumably the only reason it’s not complaining about those other problems (until you delete the entire tab and break all the circles) is that it’s only checking for one problem at a time, and the circular-reference check happens before the empty-reference check.

To actually fix the problem, you’d have to go through every circular reference, trace out the full circle, figure out where you actually want to start that chain of references, and give it a good starting point. But if you’ve got as many as it sounds like, this is going to be a royal pain, and it’s likely that the same sloppy coding that gave you all those circular references also gave you a bunch of wrong but not erroneous references that the computer won’t automatically warn you about. I’d recommend just falling back to a known good model, and if that doesn’t have all the features you want, adding the features in one at a time to it.

#3

The fact that you didn’t get an error about a circular reference in the original spreadsheet doesn’t necessarily mean there isn’t one. Excel isn’t always foolproof in reporting these errors. I’ve had circular references that seemed fine for a long time, then suddenly started reporting the error.

Did you examine the actual formulas that are supposedly circular references? Are they?

#4

Macros can be written to work on the currently open xls, and so the other files macro can be assumed to work on the currently open xls. Maybe the macro triggers the circular reference alert.

Its also possible that the references are somehow named, and so one xls’s use of those references is somehow jumping across to the other file… due to to the name being the same ?

#5

IMO / IME …

If you’ve got queer behavior you’ve got a damaged file. Once it’s damaged there’s no reliable reasoning we can make about how the damage manifests.

Identify and destroy all damaged copies and replace them with copies of the undamaged model with fresh data pasted only as values or hand-entered. Best to do this running two separate instances of Excel, not having a good and a bad copy of the model running in separate tabs of the same instance of Excel.

Anything less is tilting at windmills. You’re chasing what amounts to an infectious disease in your data space. Until you kill the last living example it’ll just keep reproducing.

Given more details about the version(s) of Excel you’re using, which file formats, which add-ins, etc., there may be more we can say.

#6

The original copies of the same files still show no CR. It’s only these versions that I’m getting back.

No. They’re lookups into a database (on another tab) of hardcoded numbers.

The file has macros, but no “auto_open” macros, and the error appears on opening the file.

Of the cells which ostensibly produce the errors, only one refers to a named range. And that cell is not the first one which shows up as the problem.

Well I’ve already sent “clean” versions of the model to the people who sent me the problematic ones. But I’d like to understand the underlying issue, if possible.

One other thing is that the error messages don’t seem to impede the functioning of the models in any way. But you never know.

Part of Office 10. “.xlsm” extension. My employer has some custom add-ins for things like peer review documentation and such.

#7

If your model design can tolerate it I’d try this: Unprotect the input cells and protect the whole workbook. Don’t give the troublesome branch office the unprotect password.

The other thing to do is establish a screen sharing session between yourself and the person who’s creating the damage. Watch them open a fresh copy of a known good model in a fresh instance of Excel and watch them use the model.

My bet is they’re doing some wild cut-and-paste or autofill move that you’d never imagine which has the effect of making your “database” become self-referential. Double likely if you’ve got array formulas or pivot tables in there someplace.
The good news is you don’t have cross-version issues, nor do you have a mix of traditional xls and new xlsx/xlsm file formats. *That *can of worms is very large and tastes very bad.