Erroneous circular reference warnings in Excel 2008

[fro...@officeformac.com]

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]

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.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

[fro...@officeformac.com]

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]

In article <ee8b...@webcrossing.caR9absDaxw>,
fro...@officeformac.com wrote:

> 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]

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)?

[fro...@officeformac.com]

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

[fro...@officeformac.com]

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]

On 2/4/08 4:21 PM, in article ee8b...@webcrossing.caR9absDaxw, "fro...@officeformac.com" <fro...@officeformac.com> wrote:

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.

[JE McGimpsey]

In article <ee8b...@webcrossing.caR9absDaxw>, Badger <> wrote:

> 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?

[rel...@stroudwaterassociates.com]

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

[rel...@stroudwaterassociates.com]

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. (patm...@microsoft.com)

Thanks,

Pat

On 3/24/08 7:58 AM, in article ee8bf...@webcrossing.caR9absDaxw, "Gary
Loch" <ga...@presentias.com> wrote:

--
Pat McMillan
Macintosh Business Unit
Microsoft Corp.
This posting is provided ³AS IS² with no warranties, and confers no rights.

[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

Pat McMillan wrote:
>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. (patm...@microsoft.com)
>
>Thanks,
>
>Pat
>
>On 3/24/08 7:58 AM, in article ee8bf...@webcrossing.caR9absDaxw, "Gary
>Loch" <ga...@presentias.com> wrote:
>
>> 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]

[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

On 3/25/08 7:06 PM, in article 81b6028109746@uwe, "Bizzuka" <u42420@uwe>
wrote:

--

[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 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.

[kanj...@officeformac.com]

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 patm...@microsoft.com I would
be very grateful.

Thanks,

Pat

On 5/4/08 11:38 PM, in article ee8bf...@webcrossing.caR9absDaxw,
"kanj...@officeformac.com" <kanj...@officeformac.com> wrote:

--

[kanj...@officeformac.com]

> 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 patm...@microsoft.com I would
> be very grateful.
>
> Thanks,
>
> Pat
>
>
> On 5/4/08 11:38 PM, in article ee8bf...@webcrossing.caR9absDaxw,

> This posting is provided &#65533;AS IS&#65533; with no warranties, and confers no rights.
>
>
> I just sent you the file

[fro...@officeformac.com]

I just downloaded and installed SP1 and my circular reference problem still exists.

I am still trying to pare down the sheet to something that exhibits the problem but does not contain all our proprietary stuff. The size of the workbook may in fact be part of the problem. It's not outrageous, but it is 16 sheets, some of which have over 300 rows and a few dozen columns.

Duncan

[Pat McMillan]

Thanks for these reports, Duncan.

Regarding the erroneous circular reference problem: We tried to get a fix
into SP1 but didn't quite make the deadline for that release. We are testing
a fix now and hope to release it as soon as possible. If you can provide me
with a file that reproduces the problem, I can add it to our testing matrix,
just to be sure. Please send to patm...@microsoft.com.

Regarding the bogus compatibility errors: Thanks for the report! I'll be
posting a bug for that and we will consider it for a future update.

Regarding the update issue with cell references: I'll look into that one and
post a bug if we don't already have one. I'll let you know if I need more
info, but it looks like you've done your homework already!

Thanks again,

Pat

On 5/14/08 9:56 AM, in article ee8bf...@webcrossing.caR9absDaxw,
"fro...@officeformac.com" <fro...@officeformac.com> wrote:

--

Pat McMillan
Macintosh Business Unit
Microsoft Corp.

This posting is provided ³AS IS² with no warranties, and confers no rights.

[Pat McMillan]

Hi again, Duncan.

With regard to the problem of the linked cell not updating, it looks like I
am going to need more information from you, or some files to repro this.
Following your general steps, and using fairly small tab delimited data
sets, I don't see this problem. When I paste the data into a range on sheet2
that overlaps the cell linked to from sheet1, then the value of the cell on
sheet1 updates as expected. It could be that the problem you're seeing is
related to the size of the data being pasted, the size of the range you're
pasting into, the type of data in the source or destination range, or
something different. Is there any chance you could share an Excel file and
text file that reproduces this? If you can, please send it to me at
patm...@microsoft.com.

Thanks,

Pat

On 5/14/08 9:56 AM, in article ee8bf...@webcrossing.caR9absDaxw,
"fro...@officeformac.com" <fro...@officeformac.com> wrote:

--

Pat McMillan
Macintosh Business Unit
Microsoft Corp.

This posting is provided ³AS IS² with no warranties, and confers no rights.

[JoelR]

Pat and Duncan, I too am having this same problem and have tried numerous
things to fix it with no success. I would be more than happy to send my
microsoft excel 2008 for mac worksheet to you, Duncan, if it would help solve
this problem.

Let me know and I will send it,

Joel

Pat McMillan wrote:
>Hi again, Duncan.
>
>With regard to the problem of the linked cell not updating, it looks like I
>am going to need more information from you, or some files to repro this.
>Following your general steps, and using fairly small tab delimited data
>sets, I don't see this problem. When I paste the data into a range on sheet2
>that overlaps the cell linked to from sheet1, then the value of the cell on
>sheet1 updates as expected. It could be that the problem you're seeing is
>related to the size of the data being pasted, the size of the range you're
>pasting into, the type of data in the source or destination range, or
>something different. Is there any chance you could share an Excel file and
>text file that reproduces this? If you can, please send it to me at
>patm...@microsoft.com.
>
>Thanks,
>
>Pat
>
>On 5/14/08 9:56 AM, in article ee8bf...@webcrossing.caR9absDaxw,
>

>> I just downloaded and installed SP1 and my circular reference problem still
>> exists.

>[quoted text clipped - 5 lines]
>>
>> Duncan
>

[Pat McMillan]

Thanks Joel. Can you tell me which problem you're referring to? The
erroneous circular reference problem or the issue of certain linked cells
not updating when pasting data over the source range?

If it's the erroneous circular reference issue, we don't need any more files
for that. We have a fix ready that we hope we will be able to release soon
to solve the problem.

Thanks,

Pat

On 5/25/08 5:28 PM, in article 84b41a48c20ff@uwe, "JoelR" <u43825@uwe>
wrote:

--

[JoelR via MacKB.com]

Pat, it is the erroneous circular reference issue so I guess I'll just wait
for the fix. Thanks a lot,

Joel

Pat McMillan wrote:
>Thanks Joel. Can you tell me which problem you're referring to? The
>erroneous circular reference problem or the issue of certain linked cells
>not updating when pasting data over the source range?
>
>If it's the erroneous circular reference issue, we don't need any more files
>for that. We have a fix ready that we hope we will be able to release soon
>to solve the problem.
>
>Thanks,
>
>Pat
>
>On 5/25/08 5:28 PM, in article 84b41a48c20ff@uwe, "JoelR" <u43825@uwe>
>wrote:
>
>> Pat and Duncan, I too am having this same problem and have tried numerous
>> things to fix it with no success. I would be more than happy to send my

>[quoted text clipped - 30 lines]
>>>>
>>>> Duncan
>

--
Message posted via MacKB.com
http://www.mackb.com/Uwe/Forums.aspx/excel/200805/1

[b...@personick.com]

On Monday, February 4, 2008 at 10:55:19 AM UTC-5, fro...@officeformac.com wrote:
> 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

For what it's worth, I get this issue all the time in Excel 2013, and I remember it happening in Excel 2010 as well.

It's so frustrating, but it seems to be when you use lookup values across worksheets or several layers deep, excel thinks that a calculation is circular before hitting a non-circular ending, there is probably some logic int he checking which is intended to keep excel quick while finding circular iterations that flags it as circular, and then another piece that must "prove" it is circular so that the calculation engine ends up displaying "0"s or drawing blue lines.