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