G
Guest
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.
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.