inserting subtotals causes recalculation

[Tom Wilson]

Since I "upgraded" from Excel 2000 to Excel 2003 (SP2), I've found that
inserting subtotals using the data/ subtotal command causes a recalculation
of all open workbooks, apprently once for each subtotal inserted. The only
solution offered on the MS KB is to turn calculation to manual, but I found
that to have no effect on the problem.

The problem makes the subtotal function a real nuisance for me - I have to
close a 10 file financial model every time I want to insert subtotals.

Any suggestions?

 

[Ronald Dodge]

I generally by default use manual calculation and I haven't found anything
that does a full calculation on all workbooks for as long as the calculation
mode is set to manual. The only way that I been able to have a full recalc
done is by pressing Alt-Ctrl-F9, which I actually prefer that setup for when
I do want manual calculation. Of course, I have no trust in the plain old
F9 key as it has not properly calculated my stuff when it gets into more
complex type formulas.

The only thing is, with my machine center files, which has massive amount of
calculations from the up to a full year's worth of data with the most
detailed level being by shift and day for each of the different categories,
I created my own set of subtotal rows on the various worksheets, which then
I also created my own set of formulas that I can copy and paste.

Of course, I also use VBA to control the process of those calculations and I
also use VBA to help aid in calculating some of those values, in particular
most of the time I have VBA doing array type stuff so as to avoid the SUMIF
and COUNTIF functions as those 2 functions are very expensive to use from a
CPU usage and memory usage.

Another option, though the setup is different, you can also use pivot tables
to create your subtotals and grand totals from your data table.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Tom Wilson]

Thanks for your reply, Ronald. I too work with manual calculation most of
the time to avoid slow response with a large financial model. I'm beginning
to think there must be something unique about my setup. I use some custom
functions to bring in some data from a series of tables. I wonder if the
custome functions, or perhaps in particular the Volatile setting in those
functions could be causing the problem. At any rate, I didn't have the
problem with the previous version of Excel and nothing else has changed.

Tom Wilson