I
ICE9
Is there any way to make an entire sheet calculate before recalculatin
other sheets? Or perhaps set one sheet to always calculate last?
I will attempt to explain the problem, but it is a bit complicated.
The sheet that causes a problem is an imported data table wit
additional formulas to the right of the data table.
1. All data is in record format so the formulas on the right onl
calculate based on values in the same row.
2. There are over 4,000 records.
3. The formulas on each row perform VLOOKUPs on static tables in othe
sheets to determine values.
4. A summary sheet performs LOTS of SUMPRODUCT calculations on th
entire data sheet.
The problem is that the summary sheet only needs to be calculated afte
the entire data sheet has been calculated. Normally, this is how i
happens. Unfortunately it sometimes calculates the entire Summar
sheet after recalcutating each individual row of the data sheet. Thi
causes the summary to be calculated over 4,000 times taking 10 to 1
minutes.
Events which cause the Summary sheet to be recalculated for each row o
the Data sheet.
1. Changing the AutoFilter on a calculated column in the data sheet.
2. Using Fill Down on a single calculated collumn of the data shee
(only 10% of the time, the other 90% it recalcs the whole data shee
first, seemingly random)
I can usually avoid the Fill Down problem by instead using fill down o
all of the calculated columns of the data sheet. For some reason thi
forces Excel to calculate the data sheet first, then the summar
sheet.
Additional complications....
There are actually 11 separate data sheets. Each are summarized by th
SUMPRODUCT functions on the Summary sheet. None of the data sheets ar
dependent on each other, and none of them are dependent on calculate
cells in other sheets. When doing a Recalc Workbook it actuall
recalcs the Summary sheet over 50,000 times.
So the simple question is.... How do i set the Summary sheet to onl
calculate manually or last? I cannot use the Options in Excel sinc
this spreadsheet is used by others so it must be a solution as part o
the document
other sheets? Or perhaps set one sheet to always calculate last?
I will attempt to explain the problem, but it is a bit complicated.
The sheet that causes a problem is an imported data table wit
additional formulas to the right of the data table.
1. All data is in record format so the formulas on the right onl
calculate based on values in the same row.
2. There are over 4,000 records.
3. The formulas on each row perform VLOOKUPs on static tables in othe
sheets to determine values.
4. A summary sheet performs LOTS of SUMPRODUCT calculations on th
entire data sheet.
The problem is that the summary sheet only needs to be calculated afte
the entire data sheet has been calculated. Normally, this is how i
happens. Unfortunately it sometimes calculates the entire Summar
sheet after recalcutating each individual row of the data sheet. Thi
causes the summary to be calculated over 4,000 times taking 10 to 1
minutes.
Events which cause the Summary sheet to be recalculated for each row o
the Data sheet.
1. Changing the AutoFilter on a calculated column in the data sheet.
2. Using Fill Down on a single calculated collumn of the data shee
(only 10% of the time, the other 90% it recalcs the whole data shee
first, seemingly random)
I can usually avoid the Fill Down problem by instead using fill down o
all of the calculated columns of the data sheet. For some reason thi
forces Excel to calculate the data sheet first, then the summar
sheet.
Additional complications....
There are actually 11 separate data sheets. Each are summarized by th
SUMPRODUCT functions on the Summary sheet. None of the data sheets ar
dependent on each other, and none of them are dependent on calculate
cells in other sheets. When doing a Recalc Workbook it actuall
recalcs the Summary sheet over 50,000 times.
So the simple question is.... How do i set the Summary sheet to onl
calculate manually or last? I cannot use the Options in Excel sinc
this spreadsheet is used by others so it must be a solution as part o
the document