Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel 2003 calculates new on closing/opening outlines
Hello,
I have a very big workbook containing thousands of formulas and outlines as well as self-programmed functions (in an AddIn). Now I recognized that Excel recalculates nearly my complete workbook on closing/opening outlines. So I took a closer look at it and found that Excel recalculates all those formulas again which are located outside the outline - but which use cells in side that outline. For example if formula in cell A1 contains "=B5*A2" and then I open/close an outline so that B5 becomes visible/invisible then formula in A1 is recalculated. But this does not happen in any Excel-version before Excel 2003. So I have two questions: 1. Is this a bug or a feature of Excel2003? 2. What can I do against it so that I still can use my workbook? Thanks for your trouble in advance. Kind regards, Dirk |
#2
|
|||
|
|||
xl2003 added an option to =subtotal().
It used to ignore cells that were hidden by an autofilter. It was enhanced to ignore cells that were hidden manually (the first parm changes (9 becomes 109 for example)). I'm guessing that excel figures that there might be a reason to recalc when you hide/unhide rows. Maybe you could just turn the calculation to manual (tools|options|calculation tab) And toggle it back on when you need it. Dirk Flakowski wrote: Hello, I have a very big workbook containing thousands of formulas and outlines as well as self-programmed functions (in an AddIn). Now I recognized that Excel recalculates nearly my complete workbook on closing/opening outlines. So I took a closer look at it and found that Excel recalculates all those formulas again which are located outside the outline - but which use cells in side that outline. For example if formula in cell A1 contains "=B5*A2" and then I open/close an outline so that B5 becomes visible/invisible then formula in A1 is recalculated. But this does not happen in any Excel-version before Excel 2003. So I have two questions: 1. Is this a bug or a feature of Excel2003? 2. What can I do against it so that I still can use my workbook? Thanks for your trouble in advance. Kind regards, Dirk -- Dave Peterson |
#3
|
|||
|
|||
Hello Dave,
thanks for your answer. But one additional question: is there no way to force Excel2003 to work like older versions? In this way Excel is nearly no more usable for all our customers! Thanks for your trouble in advance. Dirk On Thu, 08 Sep 2005 09:30:40 -0500, Dave Peterson wrote: xl2003 added an option to =subtotal(). It used to ignore cells that were hidden by an autofilter. It was enhanced to ignore cells that were hidden manually (the first parm changes (9 becomes 109 for example)). I'm guessing that excel figures that there might be a reason to recalc when you hide/unhide rows. Maybe you could just turn the calculation to manual (tools|options|calculation tab) And toggle it back on when you need it. |
#4
|
|||
|
|||
This is a long shot because I don't have any similar workbooks to test it on, but perhaps you might like to try going to 'Options' 'Calculation' and selecting
'Automatic except tables'? It sounds like it might stop the calculation of anything tabular which may have its contents hidden/unhidden. Or it may be for something else altogether. BizMark Quote:
|
#5
|
|||
|
|||
Not that I know.
Dirk Flakowski wrote: Hello Dave, thanks for your answer. But one additional question: is there no way to force Excel2003 to work like older versions? In this way Excel is nearly no more usable for all our customers! Thanks for your trouble in advance. Dirk On Thu, 08 Sep 2005 09:30:40 -0500, Dave Peterson wrote: xl2003 added an option to =subtotal(). It used to ignore cells that were hidden by an autofilter. It was enhanced to ignore cells that were hidden manually (the first parm changes (9 becomes 109 for example)). I'm guessing that excel figures that there might be a reason to recalc when you hide/unhide rows. Maybe you could just turn the calculation to manual (tools|options|calculation tab) And toggle it back on when you need it. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 locks up after startup | Excel Discussion (Misc queries) | |||
Excel Re-calculation 2000 compared to 2003 | Excel Discussion (Misc queries) | |||
Problems using Excel 2000 to open/save file saved in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 crashes loading excel files created Excel 2000 | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) |