1

We have a workbook that we have been using daily for many years in Excel 2003.

We are now migrating to Excel 2010, and have encountered the following problem.

When the workbook is opened in 2010 (32-bit version on 64-bit Windows 7) (clean open, no other workbooks present) and we try calculating any of the worksheets (even the simplest one), we get the following error:

"Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command."

This does not seem to happen in the 64-bit version of 2010.

If we open the workbook in Excel 2003, there are no calculation issues. If we open the workbook in 2003 and calculate one sheet (shift-F9), or even calculate one cell that is a trivial reference to another cell (=E4), and then save the workbook, we do not get quite the same behaviour: when opened in 2010, we can calculate various sheets (shift-F9 or worksheet.calculate), but when running a macro to calculate all sheets in the workbook, it crashes at some point on one of the sheets. Again, this does not happen in the 64-bit version of 2010.

Has anyone else encountered this problem with Excel 2003-workbooks in Excel 2010, 32-bit version? I cannot find any references to a problem of this description anywhere. Thanks.

4
  • Did you convert the file before calculating?
    – CharlieRB
    Jul 30, 2013 at 19:00
  • I think I'm thinking the same as @CharlieRB. Have you tried saving the file as xlsm / xlsx? There might be some info from the 2003 version that isn't being understood correctly for Excel 2010. Saving it as the new file type might correct that.
    – Joseph
    Jul 31, 2013 at 2:55
  • Well, my concern isn't really with "correcting" the spreadsheet. The spreadhseet gets produced by a fairly complex automated process and needs to be operable for a number of fairly complex processes that follow. I can perhaps "correct" it in one instance for 2010-32bit, but that gives me no guarantees that it will be corrected in future instances unless I know and understand the problem exactly. In other words, I am looking to understand what the problem is, not just figure out a hack to get around it. (Indeed, if you re-read my post, I indicate one way to "correct" it already.) Aug 1, 2013 at 3:41
  • To clarify: Is this a single workbook that has been in use for multiple years or is this a new workbook that has been produced by an automated process with a fresh copy coming out daily for several years? Jan 25, 2022 at 15:31

1 Answer 1

0

Okay, time to give an answer that's going to go into negative votes territory and stay there...

Yes, I can tell you that it's real: you don't have a circular reference, anywhere, and Excel is telling you that you've got one... somewhere.

And sometimes, it will tell you that it's Over here, Look! in a cell that definitely does not participate in a circular reference chain and often has no dependents at all.

Microsoft are never going to acknowledge this problem and they are never going to fix it.

A significant number of contributors to this site, and others, are never going to acknowledge that you do actually have a sheet with no circular references whatsoever, which is nevertheless raising a spurious error message. They, too are trapped in a spurious circular loop, and will only ever respond by giving you increasingly-patronising responses that explain how you will easily fix the circular reference that you do not actually have, by following a circular-reference resolution dialog that you'll never see.

The most galling version of this bug is the case when you really did have a circular error: you found it, you fixed it, and now you keep getting spurious 'circular reference' errors in cells with no dependents, no precedents, and no conditional formatting.

And, I cannot emphasise this enough, you do not have a circular reference.

Trust yourself, and do not give up and switch on iterative calculation as a mitigation.

Now for the bad news: there's no deterministic fix.

But let's try one anyway, and tell you how to use it as kludge:

You can sometimes fix this by deleting array formulas, saving and closing the file, and reinstating the arrays.

You can sometimes fix this by deleting lookup and conditional-sum formulas, saving and closing the file, and reinstating the lookups and conditionals.

The problem will recur, a few days later, when deep-calculating the sheet after 'fixing' it this way.

But, if you are the kind of person who always looks for lost quarters in the 'out' slot of a parking meter, I will offer you a suggestion that'll make you feel dirty:

Save the temporarily-fixed file as read-only, and get your sysadmin to set ownership and permissions so that this 'master' copy is never altered by your users.

The file is then a 'landmine' for anyone who takes it elsewhere and clicks 'Save As...' but the reappearance of the 'Circular error' message is now their problem.

...A a fix that might work, but I never succeeded with it:

I have never succeeded in eradicating the problem by removing the CalculationChain object from the xml and rebuilding the file: but try it anyway because I've heard it worked for someone, once.

...And the fix that actually works, but no-one wants to use:

I have only ever succeeded in fixing it by opening a new sheet, and copying across the formulas, formats, and conditional formats - in separate copy operations, not together with a simple ctrl-c and ctrl-v - one sheet at a time, and recreating the Names collection by hand.

I have verified, by hash signatures, that the text and formulae of the old and new sheets were identical and I do not doubt that the formulas were indeed identical.

And the new files worked without ever raising the spurious 'circular references' error again.

That's it. All of it. There is no other reliable answer.

Footnotes:

If you've come here in search of "Excel+Spurious+Circular+References" - and someone will, every few weeks or so, long after David Macintosh and I are the subject of intense debate among archaeologists examining the high-status grave goods and peculiar items 'of ritual significance' in our elaborately-decorated sarcophagi - I can well believe that you find this advice extremely unappetising; and I fully understand that you will probably file it away for use as a last resort.

And I feel that I should apologise if you only find this after reading several identically brilliant-but-misguided suggestions that your problem is your fault for using too many IF() branches, and that you shouldn't have used them at all. Those particular cultists are the reason for the sinister inscriptions that archaeologists really ought to read more carefully, and for the unexplained disappearance of several members of their excavation team.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.