|
Related Topics:
| Dynamic Reference to Worksheet in Excel v.X - Is it possible to select a worksheet reference in a formula? For example, I have a series of for each player of a game, Player1, Player2, and so on. Each player's sheet has a useful statisic in cell E14 that I would like to..
Excel 2003 tabs and reference cells - I'm using a worksheet that has several tabs. I need to pull select from all tabs into one Summary tab. I've create the I need for the tabs I have now but there will be others. Can I create a tab so that it can be..
Solver in Excel 2008? - Hi, I just cranked up the student edition of Office 2008 and can't find the solver in excel or in any tell me solver was included, I need it for classes!
Excel 2008 for Mac - I could not use the arrow keys on my keyboard to move a cell to left, right, up and Key board is working fine for other Pls help
Excel 2008 windows - Excel keeps opening the file in a very small size windows (4 rows by 4 columns). Even when I resize and close it, it still does it each and every time. This only occurs for the same file (the data is more than 4 by 4). How do I fix Thanks.
|
|
|
Next: Excel: Using template columns
|
| Author |
Message |
External
Since: Feb 04, 2008
Posts: 5
|
(Msg. 1) Posted: Mon Feb 04, 2008 7:55 am
Post subject: Erroneous circular reference warnings in Excel 2008
Archived from groups: microsoft>public>mac>office>excel (more info?)
|
|
|
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.<br>
<br>
Opening the workbook in Excel 2004 gives no such errors.<br>
<br>
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.<br>
<br>
It's saved in '97-04 compatibility format, if that matters, but it always has been.<br>
<br>
Any ideas?<br>
<br>
Duncan |
|
| Back to top |
|
 | |
External
Since: Feb 04, 2008
Posts: 5
|
(Msg. 2) Posted: Mon Feb 04, 2008 12:13 pm
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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).<br>
<br>
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.<br>
<br>
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.<br>
The error message is the less-helpful "references that caused it can not be listed for you" variety.<br>
<br>
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.<br>
<br>
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?)<br>
<br>
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.<br>
<br>
Duncan |
|
| Back to top |
|
| |
External
Since: Feb 04, 2008
Posts: 5
|
(Msg. 3) Posted: Mon Feb 04, 2008 1:13 pm
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
Duncan >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Feb 04, 2008
Posts: 5
|
(Msg. 4) Posted: Mon Feb 04, 2008 1:21 pm
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Mar 07, 2004
Posts: 3826
|
(Msg. 5) Posted: Mon Feb 04, 2008 1:52 pm
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
In article <ee8bff6.1 DeleteThis @webcrossing.caR9absDaxw>,
frobozz DeleteThis @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? >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 07, 2004
Posts: 3826
|
(Msg. 6) Posted: Wed Feb 27, 2008 4:29 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Mar 05, 2008
Posts: 2
|
(Msg. 7) Posted: Wed Mar 05, 2008 6:10 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Feb 4, 10:55 am, frob....DeleteThis@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
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 >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 05, 2008
Posts: 2
|
(Msg. 8) Posted: Wed Mar 05, 2008 6:43 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Mar 5, 9:10 am, rel... RemoveThis @stroudwaterassociates.com wrote:
> On Feb 4, 10:55 am, frob... RemoveThis @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
>
> 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 >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 24, 2008
Posts: 1
|
(Msg. 9) Posted: Mon Mar 24, 2008 6:58 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 05, 2008
Posts: 41
|
(Msg. 10) Posted: Mon Mar 24, 2008 8:54 pm
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. (patmcmil@microsoft.com)
Thanks,
Pat
On 3/24/08 7:58 AM, in article ee8bff6.11 DeleteThis @webcrossing.caR9absDaxw, "Gary
Loch" <gary DeleteThis @presentias.com> wrote:
> 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
Macintosh Business Unit
Microsoft Corp.
This posting is provided łAS IS˛ with no warranties, and confers no rights. >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 26, 2008
Posts: 1
|
(Msg. 11) Posted: Wed Mar 26, 2008 2:06 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. (patmcmil@microsoft.com)
>
>Thanks,
>
>Pat
>
>On 3/24/08 7:58 AM, in article ee8bff6.11.DeleteThis@webcrossing.caR9absDaxw, "Gary
>Loch" <gary.DeleteThis@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]
>> I have checked and double checked my calcs and I'd be happy to send it to you
>> for looking over.
> >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 05, 2008
Posts: 41
|
(Msg. 12) Posted: Wed Mar 26, 2008 2:06 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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:
> 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. (patmcmil@microsoft.com)
>>
>> Thanks,
>>
>> Pat
>>
>> On 3/24/08 7:58 AM, in article ee8bff6.11.RemoveThis@webcrossing.caR9absDaxw, "Gary
>> Loch" <gary.RemoveThis@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]
>>> I have checked and double checked my calcs and I'd be happy to send it to
>>> you
>>> for looking over.
>>
>
--
Pat McMillan
Macintosh Business Unit
Microsoft Corp.
This posting is provided łAS IS˛ with no warranties, and confers no rights. >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Feb 11, 2005
Posts: 566
|
(Msg. 13) Posted: Fri Mar 28, 2008 8:05 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 3/27/08 11:03 PM, in article 81cfa80c49855@uwe, "WAR" <u42490@uwe> wrote:
> 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.
--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: May 04, 2008
Posts: 2
|
(Msg. 14) Posted: Sun May 04, 2008 10:38 pm
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
External
Since: Mar 05, 2008
Posts: 41
|
(Msg. 15) Posted: Thu May 08, 2008 9:37 am
Post subject: Re: Erroneous circular reference warnings in Excel 2008 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 patmcmil.RemoveThis@microsoft.com I would
be very grateful.
Thanks,
Pat
On 5/4/08 11:38 PM, in article ee8bff6.17.RemoveThis@webcrossing.caR9absDaxw,
"kanjanop@officeformac.com" <kanjanop.RemoveThis@officeformac.com> wrote:
> 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
Macintosh Business Unit
Microsoft Corp.
This posting is provided łAS IS˛ with no warranties, and confers no rights. >> Stay informed about: Erroneous circular reference warnings in Excel 2008 |
|
| Back to top |
|
| |
|
FAQ
Search
Profile
Private Messages
Log in/Register/Password
