G
Guest
Hi
I have a workbook contains 3 sheets
"2002": The first is data for 2002. There are about 35,000 rows of data.
Each row has 5 identifying features (ie columns) plus two of actual numbers.
"1995": The second sheet is similar data for 1995. However there are only
about 8,000 records and only 4 identifying factors.
(The identifying factors are pre-defined lists, with between 8 and 33
options available.)
The third sheet is a summary sheet. What I want to do is dynamically return
a 2D table that summarises any combination of that data (including
aggregating and filtering). I have successfully implemented this using the
following formula:
=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D18)*
(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*
IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B$4&"35393")=$F$4))*
IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B$5&"35393")=$F$5))*
(INDIRECT($D$6&"!J2:J35393")))
D6 contains either 2002 or 1995 (ie the sheet names).
B2:B5 contains the letters of the columns which are being filtered and/or
displayed (dynamically retrieved by simple formulae)
F4:F5 contain possible filters
This formula is copied across a 33 by 33 range (large enough to cover all
possible combinations of rows/columns)
Unfortunately this calculates very slowly - it currently takes about
30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to
recalculate a lot as you need to change other options to set up the table how
you want it.
I have read a few sites on optimising speed of calculations etc, and they
tend to suggest using alternatives to the sum() function but I am not sure
how they apply here given the versatility I am hoping for.
Any advice or suggestions would be very much apprecaited
Regards
Revontulet
I have a workbook contains 3 sheets
"2002": The first is data for 2002. There are about 35,000 rows of data.
Each row has 5 identifying features (ie columns) plus two of actual numbers.
"1995": The second sheet is similar data for 1995. However there are only
about 8,000 records and only 4 identifying factors.
(The identifying factors are pre-defined lists, with between 8 and 33
options available.)
The third sheet is a summary sheet. What I want to do is dynamically return
a 2D table that summarises any combination of that data (including
aggregating and filtering). I have successfully implemented this using the
following formula:
=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D18)*
(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*
IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B$4&"35393")=$F$4))*
IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B$5&"35393")=$F$5))*
(INDIRECT($D$6&"!J2:J35393")))
D6 contains either 2002 or 1995 (ie the sheet names).
B2:B5 contains the letters of the columns which are being filtered and/or
displayed (dynamically retrieved by simple formulae)
F4:F5 contain possible filters
This formula is copied across a 33 by 33 range (large enough to cover all
possible combinations of rows/columns)
Unfortunately this calculates very slowly - it currently takes about
30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to
recalculate a lot as you need to change other options to set up the table how
you want it.
I have read a few sites on optimising speed of calculations etc, and they
tend to suggest using alternatives to the sum() function but I am not sure
how they apply here given the versatility I am hoping for.
Any advice or suggestions would be very much apprecaited
Regards
Revontulet
