ExcelBanter - View Single Post

Harlan Grove

wrote...
I am trying to make dynamic ranges that change as a list changes the
ranges fall in parts of the list. I use this custome function:
....
This will return the address of the cell 2 colums over from the nth
occurence if the item in the list.
....
I use two of this custome function joined with the sheet name to get a
range.
....
="Results!"&(Nth_Occurrence(Results!$A$1:$A$109,B $1,1,2,0))&":"&
(Nth_Occurrence(Results!$A$1:$A$109,B$1,COUNTIF(R esults!A2:A121,B1),0,0))
....

Note that the first two references to B1 are row-absolute, B$1, but the
third is fully relative, B1. Intentional? Also, the row and column
offset values differ in the two calls to your udf.

From your prior description, it looks like you want the range
corresponding to the rows with the first and last instance of B1 in
Results!A1:A109 but two columns to the right, so in column C. If so,
that range would be given by the array formula

=INDEX(Results!$1:$65536,MIN(ROW(List))-1
+MATCH(B$1,Results!$A$1:$A$109,0),2):
INDEX(Results!$1:$65536,MIN(ROW(List))-1
+MATCH(2,1/(B$1=Results!$A$1:$A$109)),2)

Hardcoding the result column allows for a slightly shorter array
formula

=INDEX(Results!$C:$C,MIN(ROW(List))-1+MATCH(B$1,Results!$A$1:$A$109,0),2):
INDEX(Results!$C:$C,MIN(ROW(List))-1+MATCH(2,1/(B$1=Results!$A$1:$A$109)),2)

Both are range references, so could be used as terms in longer array
formulas.

My result is _____Results!$C$2:$C$10_____

This looks good but I can't get any other equations to use this as a
range . The equation works great I just can not use the address
returned as a range in any other equations.
....

Your formula just produces a string for the same reason ="A1" returns
the string "A1" rather than the value of cell A1. If you want to
convert it to a range reference, you need to pass it as an argument to
the INDIRECT function. However, calling a udf twice inside a volatile
function call will produce slow recalculation.