Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you create dynamic named ranges containing array formulas?
Can you create dynamic named ranges with dynamic number of rows, containing
the mega array formula written below? It should be created in a seperated sheet if I add a values on a new row in a input sheet. Problem causing the need: I have a input sheet and depending on how many rows of data there is there, I stack the input list into four different groups above eachother, depending on the the input value in one of the columns. Now I tried in a "result sheet" to make 200 rows * 6 columns * 4 groups and intended to hide rows with no data. (the max rows in input sheet are 200) Since the groups is made of very long array formulas, I find that it doesn't recalculate itself and even if I run "Application.Calculate" it take eight seconds to update. Even if I reduce the calculation to the sheets in concern instead, it will be to slow since there will be four or five sheets with mega array formulas depending on eachother for 200 * 4 array groups * 6 columns TIMES four different Input sheets. - Following formula take only rows with "Not Evaluated" in the column/range name "INS_KVAL"(takning the value from column "INS_NU" besid "INS_KVAL") =IF(ISERR(INDEX(INS_NU;MIN(IF(INS_KVAL="Not Evaluated";ROW(INDIRECT("1:"&ROWS(INS_KVAL))));ROW (INDIRECT("1:"&ROWS(INS_KVAL))));1));"";INDEX(INS_ NU;MINSTA(IF(INS_KVAL="Not Evaluated";ROW(INDIRECT("1:"&ROWS(INS_KVAL))));ROW (INDIRECT("1:"&ROWS(INS_KVAL))));1)) If I run application.calculate it take several second to update. Even if I reduce the calculation to the sheets in concern, it will be to slow since there will be four or five sheets with mega array formulas depending on eachother. I have thought of filter it instead but take that in a seperate tread later. /Kind regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you create dynamic named ranges containing array formulas?
Hi
Sorry, but I didnīt understand fully, what do you want to do, and why. But in general, it isn't wise to try to solve all goals in one go. You have here 2 different goals: The first one is to divide a range on sheet to 4 smaller ranges. Create 4 dynamic ranges, which depends on total number of rows in table. The second is to retrieve from according range only rows which match certain condition. Here you need some formula on "result sheet" with according range as data source. Probably here you need some array formula (or a helper column on input sheet). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Marie J-son" wrote in message ... Can you create dynamic named ranges with dynamic number of rows, containing the mega array formula written below? It should be created in a seperated sheet if I add a values on a new row in a input sheet. Problem causing the need: I have a input sheet and depending on how many rows of data there is there, I stack the input list into four different groups above eachother, depending on the the input value in one of the columns. Now I tried in a "result sheet" to make 200 rows * 6 columns * 4 groups and intended to hide rows with no data. (the max rows in input sheet are 200) Since the groups is made of very long array formulas, I find that it doesn't recalculate itself and even if I run "Application.Calculate" it take eight seconds to update. Even if I reduce the calculation to the sheets in concern instead, it will be to slow since there will be four or five sheets with mega array formulas depending on eachother for 200 * 4 array groups * 6 columns TIMES four different Input sheets. - Following formula take only rows with "Not Evaluated" in the column/range name "INS_KVAL"(takning the value from column "INS_NU" besid "INS_KVAL") =IF(ISERR(INDEX(INS_NU;MIN(IF(INS_KVAL="Not Evaluated";ROW(INDIRECT("1:"&ROWS(INS_KVAL))));ROW (INDIRECT("1:"&ROWS(INS_KVAL))));1));"";INDEX(INS_ NU;MINSTA(IF(INS_KVAL="Not Evaluated";ROW(INDIRECT("1:"&ROWS(INS_KVAL))));ROW (INDIRECT("1:"&ROWS(INS_KVAL))));1)) If I run application.calculate it take several second to update. Even if I reduce the calculation to the sheets in concern, it will be to slow since there will be four or five sheets with mega array formulas depending on eachother. I have thought of filter it instead but take that in a seperate tread later. /Kind regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges and Dynamic Formulas | Excel Worksheet Functions | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Dynamic Named Ranges | Excel Discussion (Misc queries) | |||
Dynamic Named Ranges | Excel Discussion (Misc queries) | |||
dynamic named ranges | Excel Programming |