M
Marie J-son
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
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