Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Mar 15, 2019

How to make changes ranges in an array/formula without manually modifying it each time

When I copy/paste data into a spreadsheet, the array below looks at ranges in columns O and B. However, the next time I copy/paste data in the columns may be different.....column O and A, or H and L....
excel
Formulas and Functions
ILikePi's avatar
ILikePi
Copper Contributor
Mar 15, 2019

 

I believe you could use Indirect,

I am only going to update the first O reference in the below example. I am going to put the reference letter "H" in Cell A1 to change it to H or whatever letter you put in Cell A1. I have never done this (used indirect) within an array function before so I am crossing fingers.

Yours:

{=IFERROR(INDEX(O2:O245, SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

 

O2:O245 is replaced with INDIRECT(A1 & "2:"& A1 &"245")  in mine. 

 

{=IFERROR(INDEX(INDIRECT(A1 & "2:"& A1 &"245"), SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

 

If you use the Evaluate formula tool in the formulas tab and step through this you will see INDIRECT(A1 & "2:"& A1 &"245")  get replaced with H2:H245.

 

You could also build the text in a separate cell and then reference it, for example keeping A1 as "H" in Cell B1 you could have a formula = A1 & "2:" & A1 &"245" you would then see "H2:H245"

and then:

{=IFERROR(INDEX(INDIRECT(B1), SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

 

 

Good luck