Circular reference error after opening file on new computer

gr8rck

New Member
Joined
Jan 20, 2017
Messages
23
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
531
Office Version
2019
Platform
Windows
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

New Member
Joined
Jan 20, 2017
Messages
23
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

Well-known Member
Joined
Jan 27, 2010
Messages
531
Office Version
2019
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,933
Office Version
365
Platform
Windows
For reference
It's also available in 2003 & the default is off
 

Watch MrExcel Video

Forum statistics

Threads
1,096,063
Messages
5,448,181
Members
405,488
Latest member
sunbeam906

This Week's Hot Topics

Top