Circular reference error after opening file on new computer

[gr8rck]

Hi,

I have a several file that have a lot of formulas and worked fine before on another computer (older) and probably with a slightly older version of excel. When opening on my new laptop all of a sudden I'm getting circular reference error notices that I've not seen before and now my formulas don't work. I can't imagine the issue is the computer. I did try saving and opening in an older file format but didn't really help. I'm on the newest version of excel with O365.

Does anyone know why this would be happening and how to fix it?

Thanks,

gr8rck

 

[6StringJazzer]

Have you done tracing on the formulas to see where the circular references are happening? These may be deliberate with the "Enable iterative calculations" option turned on, which would not be turned on in your new computer. Did you write the formulas yourself?

Try going to Options, Formulas, Calculation Options, Enable Iterative Calculations and check the box. However, you should still make sure you understand how each formula works to ensure that there are no errors.

 

[gr8rck]

Hi,

Thanks - I think you nailed it. Is that option turned on by default in older excel versions or did it not exist back then? I understand what/where/how the calculations should be working but not entirely how they were written. I was getting other errors like "inconsistent formula for area" and a few others. Those have all gone away after checking the box.

Have you done tracing on the formulas to see where the circular references are happening? These may be deliberate with the "Enable iterative calculations" option turned on, which would not be turned on in your new computer. Did you write the formulas yourself?

Try going to Options, Formulas, Calculation Options, Enable Iterative Calculations and check the box. However, you should still make sure you understand how each formula works to ensure that there are no errors.

 

[6StringJazzer]

To my knowledge that option was never turned on by default. It was available at least as far back as Excel 2007. I don't know what the behavior was in earlier versions that don't have the option.

Normally circular references are considered an error, but there are some clever ways to use it on purpose for iterative calculations. I guess Microsoft caught up with this and explicitly added the option so it wouldn't be considered an error. This usage can be difficult to reverse engineer, so that is why I advise caution, because you could break something if you don't know exactly what was done.

 

[Fluff]

For reference
It's also available in 2003 & the default is off