My company has upgraded to Excel 2003 (yes we are behind the times) and ever
since, the primary Excel template that I use and I am a developer of has
gotten much much slower. Not exactly the upgrade I was looking for. It now
takes about 5-10 seconds on a high end computer to just open up row groups.
I would like suggestions on how to change my model to make the model faster.
Thanks.

Background:
The spreadsheet template is primarily an options analysis tool where 1
option is in a single column on 1 worksheet which is 2100 rows long (broken
into 5 row group levels) and has at least 40 example columns and that number
could grow to fill out all 256 columns possible in some rare cases but
probably at least 75-80 columns normally. There are 4 different summary
output worksheets that reference the primary sheet pulling the appropriate
information off via Offset functions so that the output summary reports can
be quickly and easily customized. I have rewritten code on the primary sheet
to move away from as many volatile functions as possible but there are some
that are just not convenient to get rid of -- I have about 20 cells per
column that have Offset functions contained within it. I use these 20 cells
to do relative comparisons between columns where the "base column" that I
compare relatively needs to vary so the Offset is the quickest way to do that
- I just have to tell it the column to look in. I could write VB code to get
rid of these offset functions, but the on the fly comparison functionality is
needed and VB code will just make that part of the model slower. As stated
earlier, the 4 different summary sheets are almost entirely offset functions
that work off a column ID as well. I don't have to have the calculation
modes turned on for these other worksheets when they are not active so is it
possible to turn this off or is it off already? Thanks for any help.

This article explains what you're run into:

http://support.microsoft.com/kb/918621

--
Jim
"KMH" wrote in message
...
| My company has upgraded to Excel 2003 (yes we are behind the times) and
ever
| since, the primary Excel template that I use and I am a developer of has
| gotten much much slower. Not exactly the upgrade I was looking for. It
now
| takes about 5-10 seconds on a high end computer to just open up row
groups.
| I would like suggestions on how to change my model to make the model
faster.
| Thanks.
|
| Background:
| The spreadsheet template is primarily an options analysis tool where 1
| option is in a single column on 1 worksheet which is 2100 rows long
(broken
| into 5 row group levels) and has at least 40 example columns and that
number
| could grow to fill out all 256 columns possible in some rare cases but
| probably at least 75-80 columns normally. There are 4 different summary
| output worksheets that reference the primary sheet pulling the appropriate
| information off via Offset functions so that the output summary reports
can
| be quickly and easily customized. I have rewritten code on the primary
sheet
| to move away from as many volatile functions as possible but there are
some
| that are just not convenient to get rid of -- I have about 20 cells per
| column that have Offset functions contained within it. I use these 20
cells
| to do relative comparisons between columns where the "base column" that I
| compare relatively needs to vary so the Offset is the quickest way to do
that
| - I just have to tell it the column to look in. I could write VB code to
get
| rid of these offset functions, but the on the fly comparison functionality
is
| needed and VB code will just make that part of the model slower. As
stated
| earlier, the 4 different summary sheets are almost entirely offset
functions
| that work off a column ID as well. I don't have to have the calculation
| modes turned on for these other worksheets when they are not active so is
it
| possible to turn this off or is it off already? Thanks for any help.
|
|

Thanks, but is there any way to turn off auto calculation when opening and
closing row groups only rather than going to manual calculation even if it
must be done via VB ? This would be like turning calculation mode off for
tables?

Thanks
Ken




"Jim Rech" wrote:

This article explains what you're run into:

http://support.microsoft.com/kb/918621

--
Jim
"KMH" wrote in message
...
| My company has upgraded to Excel 2003 (yes we are behind the times) and
ever
| since, the primary Excel template that I use and I am a developer of has
| gotten much much slower. Not exactly the upgrade I was looking for. It
now
| takes about 5-10 seconds on a high end computer to just open up row
groups.
| I would like suggestions on how to change my model to make the model
faster.
| Thanks.
|
| Background:
| The spreadsheet template is primarily an options analysis tool where 1
| option is in a single column on 1 worksheet which is 2100 rows long
(broken
| into 5 row group levels) and has at least 40 example columns and that
number
| could grow to fill out all 256 columns possible in some rare cases but
| probably at least 75-80 columns normally. There are 4 different summary
| output worksheets that reference the primary sheet pulling the appropriate
| information off via Offset functions so that the output summary reports
can
| be quickly and easily customized. I have rewritten code on the primary
sheet
| to move away from as many volatile functions as possible but there are
some
| that are just not convenient to get rid of -- I have about 20 cells per
| column that have Offset functions contained within it. I use these 20
cells
| to do relative comparisons between columns where the "base column" that I
| compare relatively needs to vary so the Offset is the quickest way to do
that
| - I just have to tell it the column to look in. I could write VB code to
get
| rid of these offset functions, but the on the fly comparison functionality
is
| needed and VB code will just make that part of the model slower. As
stated
| earlier, the 4 different summary sheets are almost entirely offset
functions
| that work off a column ID as well. I don't have to have the calculation
| modes turned on for these other worksheets when they are not active so is
it
| possible to turn this off or is it off already? Thanks for any help.
|
|

My guess is if there was such a neat workaround the article would have
mentioned it.

--
Jim
"KMH" wrote in message
...
| Thanks, but is there any way to turn off auto calculation when opening and
| closing row groups only rather than going to manual calculation even if it
| must be done via VB ? This would be like turning calculation mode off for
| tables?
|
| Thanks
| Ken
|
|
|
|
| "Jim Rech" wrote:
|
| This article explains what you're run into:
|
| http://support.microsoft.com/kb/918621
|
| --
| Jim
| "KMH" wrote in message
| ...
| | My company has upgraded to Excel 2003 (yes we are behind the times)
and
| ever
| | since, the primary Excel template that I use and I am a developer of
has
| | gotten much much slower. Not exactly the upgrade I was looking for.
It
| now
| | takes about 5-10 seconds on a high end computer to just open up row
| groups.
| | I would like suggestions on how to change my model to make the model
| faster.
| | Thanks.
| |
| | Background:
| | The spreadsheet template is primarily an options analysis tool where 1
| | option is in a single column on 1 worksheet which is 2100 rows long
| (broken
| | into 5 row group levels) and has at least 40 example columns and that
| number
| | could grow to fill out all 256 columns possible in some rare cases but
| | probably at least 75-80 columns normally. There are 4 different
summary
| | output worksheets that reference the primary sheet pulling the
appropriate
| | information off via Offset functions so that the output summary
reports
| can
| | be quickly and easily customized. I have rewritten code on the
primary
| sheet
| | to move away from as many volatile functions as possible but there are
| some
| | that are just not convenient to get rid of -- I have about 20 cells
per
| | column that have Offset functions contained within it. I use these 20
| cells
| | to do relative comparisons between columns where the "base column"
that I
| | compare relatively needs to vary so the Offset is the quickest way to
do
| that
| | - I just have to tell it the column to look in. I could write VB code
to
| get
| | rid of these offset functions, but the on the fly comparison
functionality
| is
| | needed and VB code will just make that part of the model slower. As
| stated
| | earlier, the 4 different summary sheets are almost entirely offset
| functions
| | that work off a column ID as well. I don't have to have the
calculation
| | modes turned on for these other worksheets when they are not active so
is
| it
| | possible to turn this off or is it off already? Thanks for any help.
| |
| |
|
|
|