Can you create dynamic named ranges containing array formulas?

[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

 

[Arvi Laanemets]

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).