| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formulas slows spreadsheet
I have a added number of array formulas in a spreadsheet that has slowed down
working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formulas slows spreadsheet
You are referring to the entire column. Do you really want to refer A:A..?
Change that to A1:A1000 or to a suitable max limit. If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a added number of array formulas in a spreadsheet that has slowed down working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formulas slows spreadsheet
Hi Jacob,
The length of the data in the column will change and possibly go past 1000. Is there a way we can modify the formual to start at A1 and go to the last cell with data in column a? Thanks "Jacob Skaria" wrote: You are referring to the entire column. Do you really want to refer A:A..? Change that to A1:A1000 or to a suitable max limit. If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a added number of array formulas in a spreadsheet that has slowed down working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formulas slows spreadsheet
Try the below
In a separate cell say J1 type the formula =MATCH(TODAY(),INDIRECT("'"&$H$1&"'!$A:$A"),1) 'Then modify the formula to replace with that cell reference.. =INDEX(OFFSET(INDIRECT("&$H$1&"!C$1"),0,0,J1,1), SMALL(IF(OFFSET(INDIRECT(""&$H$1&"!D$1"),0,0, J1,1)=$B6,ROW(OFFSET(INDIRECT(""&$H$1&"!D$1"),0,0, J1,1))),COUNTIF(OFFSET(INDIRECT(""&$H$1&"!D$1"),0, 0,J1,1),$B6))) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: Hi Jacob, The length of the data in the column will change and possibly go past 1000. Is there a way we can modify the formual to start at A1 and go to the last cell with data in column a? Thanks "Jacob Skaria" wrote: You are referring to the entire column. Do you really want to refer A:A..? Change that to A1:A1000 or to a suitable max limit. If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a added number of array formulas in a spreadsheet that has slowed down working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formulas slows spreadsheet
Correction..
In a separate cell say J1 type the formula =MATCH(TODAY(),INDIRECT("'"&$H$1&"'!$A:$A"),1) 'Then modify the formula to replace with that cell reference.. =INDEX(OFFSET(INDIRECT("'"&$H$1&"'!C$1"),0,0,J1,1) , SMALL(IF(OFFSET(INDIRECT("'"&$H$1&"'!D$1"),0,0, J1,1)=$B6,ROW(OFFSET(INDIRECT("'"&$H$1&"'!D$1"),0, 0, J1,1))),COUNTIF(OFFSET(INDIRECT("'"&$H$1&"'!D$1"), 0,0,J1,1),$B6))) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below In a separate cell say J1 type the formula =MATCH(TODAY(),INDIRECT("'"&$H$1&"'!$A:$A"),1) 'Then modify the formula to replace with that cell reference.. =INDEX(OFFSET(INDIRECT("&$H$1&"!C$1"),0,0,J1,1), SMALL(IF(OFFSET(INDIRECT(""&$H$1&"!D$1"),0,0, J1,1)=$B6,ROW(OFFSET(INDIRECT(""&$H$1&"!D$1"),0,0, J1,1))),COUNTIF(OFFSET(INDIRECT(""&$H$1&"!D$1"),0, 0,J1,1),$B6))) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: Hi Jacob, The length of the data in the column will change and possibly go past 1000. Is there a way we can modify the formual to start at A1 and go to the last cell with data in column a? Thanks "Jacob Skaria" wrote: You are referring to the entire column. Do you really want to refer A:A..? Change that to A1:A1000 or to a suitable max limit. If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a added number of array formulas in a spreadsheet that has slowed down working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formulas slows spreadsheet
Hi,
If you expect the range to keep expanding, convert it to a List (Ctrl+L). Once you convert a range to a List, it expands as data is added by rows. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Huggy" wrote in message ... Hi Jacob, The length of the data in the column will change and possibly go past 1000. Is there a way we can modify the formual to start at A1 and go to the last cell with data in column a? Thanks "Jacob Skaria" wrote: You are referring to the entire column. Do you really want to refer A:A..? Change that to A1:A1000 or to a suitable max limit. If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a added number of array formulas in a spreadsheet that has slowed down working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array formulas slows spreadsheet
You need to get rid of all those volatile functions.
-- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Correction.. In a separate cell say J1 type the formula =MATCH(TODAY(),INDIRECT("'"&$H$1&"'!$A:$A"),1) 'Then modify the formula to replace with that cell reference.. =INDEX(OFFSET(INDIRECT("'"&$H$1&"'!C$1"),0,0,J1,1) , SMALL(IF(OFFSET(INDIRECT("'"&$H$1&"'!D$1"),0,0, J1,1)=$B6,ROW(OFFSET(INDIRECT("'"&$H$1&"'!D$1"),0, 0, J1,1))),COUNTIF(OFFSET(INDIRECT("'"&$H$1&"'!D$1"), 0,0,J1,1),$B6))) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below In a separate cell say J1 type the formula =MATCH(TODAY(),INDIRECT("'"&$H$1&"'!$A:$A"),1) 'Then modify the formula to replace with that cell reference.. =INDEX(OFFSET(INDIRECT("&$H$1&"!C$1"),0,0,J1,1), SMALL(IF(OFFSET(INDIRECT(""&$H$1&"!D$1"),0,0, J1,1)=$B6,ROW(OFFSET(INDIRECT(""&$H$1&"!D$1"),0,0, J1,1))),COUNTIF(OFFSET(INDIRECT(""&$H$1&"!D$1"),0, 0,J1,1),$B6))) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: Hi Jacob, The length of the data in the column will change and possibly go past 1000. Is there a way we can modify the formual to start at A1 and go to the last cell with data in column a? Thanks "Jacob Skaria" wrote: You are referring to the entire column. Do you really want to refer A:A..? Change that to A1:A1000 or to a suitable max limit. If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a added number of array formulas in a spreadsheet that has slowed down working on the spreadsheet because it recalculates all the array formulas each time you enter / change data. The array formulas are on a separate worksheet to the main data worksheet. How do you speed this up or how can you manually recalculate the array formulas only and allow all other formulas on the main data worksheet to re-calculate automatically. I do not want to change auto recalc to manual recalc in tools options. example of one of the array formulas is; =INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6))) Thanks for the help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Slows after about 1,000 records? | Excel Discussion (Misc queries) | |||
| Vlookup slows system | Excel Worksheet Functions | |||
| Filtering slows down a macro | Excel Worksheet Functions | |||
| Excel slows down for no(?) reason... | Excel Discussion (Misc queries) | |||
| ifsum slows down calculations | Excel Worksheet Functions | |||
Linear Mode
