Erroneous circular reference warnings in Excel 2008

[frobozz]

One employee here has 2008 installed, and has been working fine with it for a week. Suddenly, he has started getting circular reference warnings when changing values in a spreadsheet. These are simply typed in values, not formulas, though of course they are referenced elsewhere in the workbook. He can change these values numerous times without error, but then it will start giving him errors, then it will stop again, etc.

Opening the workbook in Excel 2004 gives no such errors.

Bringing back a copy of the workbook from backup tapes, from a time before this started happening, still generates the erroneous error messages. So I really don't think it's a problem with the workbook.

It's saved in '97-04 compatibility format, if that matters, but it always has been.

Any ideas?

Duncan

 

[Bob Greenblatt]

One employee here has 2008 installed, and has been working fine with it for a
week. Suddenly, he has started getting circular reference warnings when
changing values in a spreadsheet. These are simply typed in values, not
formulas, though of course they are referenced elsewhere in the workbook. He
can change these values numerous times without error, but then it will start
giving him errors, then it will stop again, etc.

Opening the workbook in Excel 2004 gives no such errors.

Bringing back a copy of the workbook from backup tapes, from a time before
this started happening, still generates the erroneous error messages. So I
really don't think it's a problem with the workbook.

It's saved in '97-04 compatibility format, if that matters, but it always has
been.

Any ideas?

Duncan

I have NEVER seen a circular reference warning that was not correct. So,
look harder, there probably is one in the workbook. If you can¹t find it,
I¹ll be glad to take a look. Send me the workbook.

 

[frobozz]

Well, sure, I've never seen that warning when it wasn't true either, but I've also never used a version of Office so fresh off the presses, on such a new version of an OS (Leopard in this case).

Sadly I can't mail you a workbook, as it's proprietary data (aren't they all?) and so far I haven't been able to pare it down to a simpler example that exhibits the behavior.

Remember that what I am changing when the error message crops up is numeric data, not formulas. It is data in a table that other cells do a series of IF-VLOOKUP statements on. And this has all worked perfectly for forever in Excel 2004 on the Mac and various Excel versions under Windows.
The error message is the less-helpful "references that caused it can not be listed for you" variety.

I have parameterized the conditions where it occurs just a little bit more. It doesn't matter if you exit the cell with an arrow or the enter key after changing the data. If you don't change the data (retype the same value) it doesn't happen. It always happens the first time you change one of these cells after opening the workbook. It then seems to only happen if there has been enough of a delay before exiting a cell. For instance, I can rapidly type several values while stepping through cells and it gets no error, but then if I type the last value and wait a moment before leaving the cell and committing the value, it will get the error. Some frequencies of typing and committing values will get an error every time.

I'm familiar, for instance, with latent bugs that are eventually found when using a new compiler... so the "it never got an error before and still doesn't in Excel 2004" argument is only persuasive, not airtight. But if this is an actual circular reference that is only now being picked up by the latest Excel, why does it not happen 100% of the time? (I'm also scratching my head about it only being picked up when editing numeric values, but I suppose if that's when the circular formula gets invoked, maybe that's when it would notice it?)

I'll keep banging on it, but since he's got the only copy of 2008 so far (we're all waiting on our $10 upgrades to ship) I have to boot him off his computer to play with it, which limits my troubleshooting time.

Duncan

 

[JE McGimpsey]

Remember that what I am changing when the error message crops up is numeric
data, not formulas. It is data in a table that other cells do a series of
IF-VLOOKUP statements on. And this has all worked perfectly for forever in
Excel 2004 on the Mac and various Excel versions under Windows.<

The only time I've seen this is when a dynamic range defined via
Insert/Name/Define expanded a range beyond the expected area, causing a
CR.

Any dynamic ranges?

 

[Bob Greenblatt]

Well, sure, I've never seen that warning when it wasn't true either, but I've
also never used a version of Office so fresh off the presses, on such a new
version of an OS (Leopard in this case).

Sadly I can't mail you a workbook, as it's proprietary data (aren't they all?)
and so far I haven't been able to pare it down to a simpler example that
exhibits the behavior.

Remember that what I am changing when the error message crops up is numeric
data, not formulas. It is data in a table that other cells do a series of
IF-VLOOKUP statements on. And this has all worked perfectly for forever in
Excel 2004 on the Mac and various Excel versions under Windows.
The error message is the less-helpful "references that caused it can not be
listed for you" variety.

I have parameterized the conditions where it occurs just a little bit more. It
doesn't matter if you exit the cell with an arrow or the enter key after
changing the data. If you don't change the data (retype the same value) it
doesn't happen. It always happens the first time you change one of these cells
after opening the workbook. It then seems to only happen if there has been
enough of a delay before exiting a cell. For instance, I can rapidly type
several values while stepping through cells and it gets no error, but then if
I type the last value and wait a moment before leaving the cell and committing
the value, it will get the error. Some frequencies of typing and committing
values will get an error every time.

I'm familiar, for instance, with latent bugs that are eventually found when
using a new compiler... so the "it never got an error before and still doesn't
in Excel 2004" argument is only persuasive, not airtight. But if this is an
actual circular reference that is only now being picked up by the latest
Excel, why does it not happen 100% of the time? (I'm also scratching my head
about it only being picked up when editing numeric values, but I suppose if
that's when the circular formula gets invoked, maybe that's when it would
notice it?)

I'll keep banging on it, but since he's got the only copy of 2008 so far
(we're all waiting on our $10 upgrades to ship) I have to boot him off his
computer to play with it, which limits my troubleshooting time.

Duncan

That certainly sound strange. You are doing a good job of trying to localize
the problem. It sounds from you description that it is happening on Recalc.
Recalculation occurs whenever data is entered. The recalc process will get
interrupted when you are entering again to make sure it does not interfere
with keying. Is there a difference with Calculation set to manual. Are there
intentional circular references on the sheet that get iterated? Are the
iteration thresholds set properly (or the same as on the versions of Excel
where this works)?

 

[frobozz]

No dynamic ranges that I know of. No intentional circular references that I know of. I'll have to dig through it a little deeper to make sure though.

The recalc thing makes sense (it waits to recalc until the keying has quieted down) except that often I can enter a few values slowly (so, presumably allowing a recalc) and it won't trip the error. Beside the one that always happens at first, there is some other factor that determines whether it happens or not, that I'm not seeing, but feels cadence-based, which makes no sense at all.

I tried to set up a small test sheet with cells that do the same VLOOKUP and couldn't get it to happen. Unfortunately, the entered data is also propogated (via = statements) to other sheets in the book, which do their own similar VLOOKUPs, so it might be a matter of the quantity of those. I'll have to set up an example workbook that crosses as many sheets, to see if I can get that to do it.

Duncan

 

[frobozz]

The checkbox is labeled "Limit Iteration" in 2008, but only "Iteration" in 2004. Is that performing the same function in both cases? In any event, it is unchecked in both versions.

Duncan

 

[Bob Greenblatt]

The checkbox is labeled "Limit Iteration" in 2008, but only "Iteration" in
2004. Is that performing the same function in both cases? In any event, it is
unchecked in both versions.

Duncan

Yes, it is the same thing. I guess they thought Limit Iteration was clearer.

 

[Badger]

I'm also getting this on one of my sheets I used successfully in 2004. It's buggin.

 

[JE McGimpsey]

I'm also getting this on one of my sheets I used successfully in 2004. It's
buggin.

What are the cell contents for the cells in the Circular Reference
toolbar's Navigate Circular Reference dropdown control?

 

[rellis]

One employee here has 2008 installed, and has been working fine with it for a week. Suddenly, he has started getting circular reference warnings when changing values in a spreadsheet. These are simply typed in values, not formulas, though of course they are referenced elsewhere in the workbook. He can change these values numerous times without error, but then it will start giving him errors, then it will stop again, etc.
Opening the workbook in Excel 2004 gives no such errors.
Bringing back a copy of the workbook from backup tapes, from a time before this started happening, still generates the erroneous error messages. So I really don't think it's a problem with the workbook.
It's saved in '97-04 compatibility format, if that matters, but it always has been.
Any ideas?
Duncan

Duncan,

I too have just realized that the circular reference I have been
chasing for the past day in Excel 2008 isn't real. I did the same
test. I opened the same worksheet in Excel 2004 and then the blank
template, created a couple of years ago, in both Excel 2004 and Excel
2008. The erroneous circular reference appeared in Excel 2008, but not
in 2007 when opening the unchanged template.

I also have Excel 2007 running in Vista on my Mac. After the updates
finish installing, I will try Excel 2007. If I do not place another
post, assume that Excel 2007 did not present the same circular
reference.

Bob

 

[rellis]

Duncan,

I too have just realized that the circular reference I have been
chasing for the past day in Excel 2008 isn't real. I did the same
test. I opened the same worksheet in Excel 2004 and then the blank
template, created a couple of years ago, in both Excel 2004 and Excel
2008. The erroneous circular reference appeared in Excel 2008, but not
in 2007 when opening the unchanged template.

I also have Excel 2007 running in Vista on my Mac. After the updates
finish installing, I will try Excel 2007. If I do not place another
post, assume that Excel 2007 did not present the same circular
reference.

Bob

I know I said I wouldn't reply if Excel 2007 did not present the same
circular reference, but when I opened the same template in Excel 2007,
there was no circular reference.

Bob

 

[Gary Loch]

If it's not too late to add my erroneous circular reference issue... this is the message I'm getting:

"There is a circular reference in an open workbook, but the references that caused it cannot be listed for you."

I have checked and double checked my calcs and I'd be happy to send it to you for looking over.

 

[Pat McMillan]

Hi Gary,

We are looking into reports of this problem. If you could send me a file
that shows the problem, it would be a great help. ([email protected])

Thanks,

Pat

 

[Bizzuka]

I, too, am getting erroneous circular reference errors. They started when I
opened the workbook. I've opened this workbook for years in Office 2003 for
PC and Office 2004 for Mac with no issues. After opening it on 2007, and
converting it to that format, I could no longer open it on Office 2004 for
Mac -- even after using the conversion tool. So, today I upgraded to 2008
for the Mac, and presto... circular reference errors.

The only thing I can think of is that it might be associated with the
Analysis Toolpak add-in that is no longer present in 2008. I believe the
only formula I used from the Toolpak is the EOMONTH() formula. This
particular workbook contains 28 worksheets and also contains proprietary
information, so I cannot send it to you. The other thing that might be
unique about this workbook is that it is password protected. Don't know that
it matters, but I thought I'd throw it in there because the previous post was
also dealing with proprietary info and his sheet might also be password
protected.

Now I can tell you that I was able to clear the problem, but I cannot
pinpoint any specific logic as to why the problem cleared or even occurred.
After clicking around, I was able to get one sheet to actually disclose the
cell which was supposedly the source of the circular reference. Other sheets
simply stated the word Circular with no specific reference to a cell.

After running the audit function (which now erroneously draws precedent lines
to cells on the same worksheet that are actually on a separate sheet-- rather
than drawing a line to a spreadsheet icon that identifies the correct sheet),
I verified that this was NOT a circular reference. So, I deleted the
supposedly offending cell, copied the adjacent formula over it (which created
the identical formula which I had just deleted), and the circular reference
errors disappeared.

I saved the workbook, and reopened it. All seems fine. Also opened it in
Excel 2003 on PC, and it worked fine.

Hope that helps in some way. Hope you MS figures it out and releases a patch
soon!

John

Hi Gary,

We are looking into reports of this problem. If you could send me a file
that shows the problem, it would be a great help. ([email protected])

Thanks,

Pat

If it's not too late to add my erroneous circular reference issue... this is
the message I'm getting:

[quoted text clipped - 4 lines]

I have checked and double checked my calcs and I'd be happy to send it to you
for looking over.

 

[Pat McMillan]

Thanks a lot for the detail. This does help. We are making some headway on
this now. Thanks again for your help.

Pat

I, too, am getting erroneous circular reference errors. They started when I
opened the workbook. I've opened this workbook for years in Office 2003 for
PC and Office 2004 for Mac with no issues. After opening it on 2007, and
converting it to that format, I could no longer open it on Office 2004 for
Mac -- even after using the conversion tool. So, today I upgraded to 2008
for the Mac, and presto... circular reference errors.

The only thing I can think of is that it might be associated with the
Analysis Toolpak add-in that is no longer present in 2008. I believe the
only formula I used from the Toolpak is the EOMONTH() formula. This
particular workbook contains 28 worksheets and also contains proprietary
information, so I cannot send it to you. The other thing that might be
unique about this workbook is that it is password protected. Don't know that
it matters, but I thought I'd throw it in there because the previous post was
also dealing with proprietary info and his sheet might also be password
protected.

Now I can tell you that I was able to clear the problem, but I cannot
pinpoint any specific logic as to why the problem cleared or even occurred.
After clicking around, I was able to get one sheet to actually disclose the
cell which was supposedly the source of the circular reference. Other sheets
simply stated the word Circular with no specific reference to a cell.

After running the audit function (which now erroneously draws precedent lines
to cells on the same worksheet that are actually on a separate sheet-- rather
than drawing a line to a spreadsheet icon that identifies the correct sheet),
I verified that this was NOT a circular reference. So, I deleted the
supposedly offending cell, copied the adjacent formula over it (which created
the identical formula which I had just deleted), and the circular reference
errors disappeared.

I saved the workbook, and reopened it. All seems fine. Also opened it in
Excel 2003 on PC, and it worked fine.

Hope that helps in some way. Hope you MS figures it out and releases a patch
soon!

John

Hi Gary,

We are looking into reports of this problem. If you could send me a file
that shows the problem, it would be a great help. ([email protected])

Thanks,

Pat

If it's not too late to add my erroneous circular reference issue... this is
the message I'm getting:

[quoted text clipped - 4 lines]

I have checked and double checked my calcs and I'd be happy to send it to
you
for looking over.

 

[WAR]

I have just made the switch from Windows after 18 years.

My Excel workbook from Office 2003 contains over thirty sheets, all of them
are interlinked. I wish I there were a way I could pinpoint the formula
which is causing the problem, but as you've said, t

I have eighteen of these workbooks, with crucial data in all of them.

Whenever I open one of these workbooks, I get the message:

"There is a circular reference in an open workbook, but the references that
caused it cannot be listed for you. Try editing the last formula you entered
or removing it with the Undo command (edit menu)."

I never had this problem running my Windows-based Office. I have downloaded
Open Office and it reads my workbooks fine, as does iWork's 2008 Numbers. I
have paid a premium for Office 2008, but I cannot use Excel.

Does anyone have a fix for this?

Bizzuka:

I appreciate that you have found a way around this, but I cannot possibly go
through all my formulas hoping to find the one that is causing the problem.



The error checking utility comes up with:
"The formula in this cell refers to cells that are currently empty."

Which is yet another problem I've never had with other programs running this
very same workbook.
The circular reference toolbar is of no help. It points to a formula and an
empty cell. The exclamation mark on the formula states: "The formula in
this cell refers to cells that are currently empty."

I'm at wit's end with this.

Help!

 

[Bob Greenblatt]

I have just made the switch from Windows after 18 years.

My Excel workbook from Office 2003 contains over thirty sheets, all of them
are interlinked. I wish I there were a way I could pinpoint the formula
which is causing the problem, but as you've said, t

I have eighteen of these workbooks, with crucial data in all of them.

Whenever I open one of these workbooks, I get the message:

"There is a circular reference in an open workbook, but the references that
caused it cannot be listed for you. Try editing the last formula you entered
or removing it with the Undo command (edit menu)."

I never had this problem running my Windows-based Office. I have downloaded
Open Office and it reads my workbooks fine, as does iWork's 2008 Numbers. I
have paid a premium for Office 2008, but I cannot use Excel.

Does anyone have a fix for this?

Bizzuka:

I appreciate that you have found a way around this, but I cannot possibly go
through all my formulas hoping to find the one that is causing the problem.



The error checking utility comes up with:
"The formula in this cell refers to cells that are currently empty."

Which is yet another problem I've never had with other programs running this
very same workbook.
The circular reference toolbar is of no help. It points to a formula and an
empty cell. The exclamation mark on the formula states: "The formula in
this cell refers to cells that are currently empty."

I'm at wit's end with this.

Help!

I believe that this is a known issue with Excel 2008. Try saving and using
the workbook as an xls file. Hopefully there will be a fix coming.

 

[kanjanop]

I have the same problem with Excel 2008 for Mac. There is no CIRCULAR when I created it in MS Excel 2003. All formulas are correct as I retyped them for Mac Excel (the reason I had to build a new one for Mac Excel was it couldn't open by Mac Excel but NeoOffice could do it without any problem - Apple salesperson and I tested NeoOffice and Mac Excel in Bangkok iStudio - I really don't know why this problem occured). The CIRCULAR message is unreal as it appeared and disappeared. For MS Excel, it will constantly show CIRCULAR all the time if it is real circular. I'm willing to send my file to you (Mr. Bob Greehblatt), if you want to take a look.

Also, Mac Excel file has another problem as explained below. No problems at all when I use MS Excel 2003.

1) If I change depreciation year for fixed asset #5 (i.e. from 5 years to 10 years), Mac Excel will not respond. I have to force quit.

2) If I include depreciation of fixed asset #4 and #5, Mac Excel will not respond too.

3) I noticed that when I use "Trace Precedents" in Mac Excel, the Trace Precedents reported wrong cell. Instead of pointing to the cell in another sheet (this is correct), it points to cell in the same sheet too (this is wrong as I don't have formula link from it). Please tell Mac Excel team about this problem.

As a result, I gave up using Mac Excel and use MS Excel right now.

 

[Pat McMillan]

Thank you for your report. We are working on a fix for the circular
reference problem and hope to have an update with the fix out soon. It would
still be very valuable to have your file to use as part of our verification
of the fix. A file for the other problems you mention would also be very,
very helpful. If you could send them to me at (e-mail address removed) I would
be very grateful.

Thanks,

Pat