A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Workbook causes calculation errors



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 04:04 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Workbook causes calculation errors

I've never seen this error before, and unfortunately, my Google searches
keep bogging me down with the simple "Automatic/Manual" solution...

I have Workbook A, which contains a large amount of data/formulas (3000 rows
x 100 columns, several columns using SUMPRODUCT checks). The workbook is in
Shared mode, and due to amount of calculations it does, is left in manual
calc mode. Up until a few days ago, users could make changes to the data,
hit F9 to recalc, save, and move on. Then something happened...

Now, the workbook always displayed "Calculate" in the status bar, even after
doing a reclac or switching to automatic mode. Could I be at the 65536
dependency limit? File still seems to calculate okay, just displays message.
However, the problem is that whenever another file is opened within the same
instance of XL, it becomes "corrupt". If I open workbook B, open workbook A
and close it immediately (without doing anything in it) Workbook B is
permanently flagged as needed to calculate, and does a full calculation
everytime I hit F9. While formulas will recalc, charts do not display
correctly.

Remedies I've found to work a
1) save, close Workbook B, and reopen (no longer displays calculate, works
fine).
2) Switch to automatic mode with Workbook B, then back to manual. Still
displays "calculate", but seems to work for the most part.

If anyone else has run across something similar or has suggestions, I'd be
eager to hear. Thanks!

--
Best Regards,

Luke M


Ads
  #2  
Old June 4th, 2010, 04:58 PM posted to microsoft.public.excel.worksheet.functions
Charles Williams
external usenet poster
 
Posts: 235
Default Workbook causes calculation errors

Once an Excel session has hit one of the dependency limits it stays in
that mode even if all the workbooks are closed, unitil the Excel
session is stopped and restarted.

See http://www.decisionmodels.com/calcsecretsf.htm for more details

regards
Charles Williams
Excel MVP
The Excel Calculation Site
http://www.DecisionModels.com

I've never seen this error before, and unfortunately, my Google searches
keep bogging me down with the simple "Automatic/Manual" solution...

I have Workbook A, which contains a large amount of data/formulas (3000 rows
x 100 columns, several columns using SUMPRODUCT checks). The workbook is in
Shared mode, and due to amount of calculations it does, is left in manual
calc mode. Up until a few days ago, users could make changes to the data,
hit F9 to recalc, save, and move on. Then something happened...

Now, the workbook always displayed "Calculate" in the status bar, even after
doing a reclac or switching to automatic mode. Could I be at the 65536
dependency limit? File still seems to calculate okay, just displays message.
However, the problem is that whenever another file is opened within the same
instance of XL, it becomes "corrupt". If I open workbook B, open workbook A
and close it immediately (without doing anything in it) Workbook B is
permanently flagged as needed to calculate, and does a full calculation
everytime I hit F9. While formulas will recalc, charts do not display
correctly.

Remedies I've found to work a
1) save, close Workbook B, and reopen (no longer displays calculate, works
fine).
2) Switch to automatic mode with Workbook B, then back to manual. Still
displays "calculate", but seems to work for the most part.

If anyone else has run across something similar or has suggestions, I'd be
eager to hear. Thanks!

  #3  
Old June 4th, 2010, 07:19 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Workbook causes calculation errors

Thanks, Charles, that seems to describe what's going on. Any thoughts as to
why the Charts in Workbook B are affected so (not updating)?

As your website says, there doesn't seem to be a good way to count
dependencies, and I'm not sure if I can reduce the formulas. I'll see what I
can do.

Again, thanks for the info.

--
Best Regards,

Luke M
"Charles Williams" wrote in message
...
Once an Excel session has hit one of the dependency limits it stays in
that mode even if all the workbooks are closed, unitil the Excel
session is stopped and restarted.

See http://www.decisionmodels.com/calcsecretsf.htm for more details

regards
Charles Williams
Excel MVP
The Excel Calculation Site
http://www.DecisionModels.com

I've never seen this error before, and unfortunately, my Google searches
keep bogging me down with the simple "Automatic/Manual" solution...

I have Workbook A, which contains a large amount of data/formulas (3000
rows
x 100 columns, several columns using SUMPRODUCT checks). The workbook is
in
Shared mode, and due to amount of calculations it does, is left in manual
calc mode. Up until a few days ago, users could make changes to the data,
hit F9 to recalc, save, and move on. Then something happened...

Now, the workbook always displayed "Calculate" in the status bar, even
after
doing a reclac or switching to automatic mode. Could I be at the 65536
dependency limit? File still seems to calculate okay, just displays
message.
However, the problem is that whenever another file is opened within the
same
instance of XL, it becomes "corrupt". If I open workbook B, open workbook
A
and close it immediately (without doing anything in it) Workbook B is
permanently flagged as needed to calculate, and does a full calculation
everytime I hit F9. While formulas will recalc, charts do not display
correctly.

Remedies I've found to work a
1) save, close Workbook B, and reopen (no longer displays calculate, works
fine).
2) Switch to automatic mode with Workbook B, then back to manual. Still
displays "calculate", but seems to work for the most part.

If anyone else has run across something similar or has suggestions, I'd be
eager to hear. Thanks!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.