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

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

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.

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

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