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. Is there a way to be able to make this change without manually modifying the array each time? Perhaps inputting a letter ("H") into a cell and it will make the change from O2:O245 to H2:H245 for example? Or a drop down menu that has all the possible options available (A2:A245, B2:B245, C2:C245, etc) and that gets inputted into the array?

 

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

 

Let me know if you can help!

 

Thanks,


Greg

 

 

excel
Formulas and Functions

9 Replies

  • 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

    • gms4b's avatar
      gms4b
      Brass Contributor
      Mar 15, 2019

      ILikePi 

       

      Holy crap! That worked!!!!!

       

      So awesome......thank you so much for your help!!

       

      • ILikePi's avatar
        ILikePi
        Copper Contributor
        Mar 15, 2019

        gms4b 

        That's awesome, and interesting. 

        I've always hated array formula's because of the whole Ctrl+Enter thing. If this works you could technically build a whole formula in cell A1 (minus the curly brackets{}). and then make your array formula only the indirect i.e. ={INDIRECT(A1)} if that maintains the Ctrl enter, you could make changes without the worry of accidentally not hitting Ctrl+Enter.

        Although that only works if you only have the formula in 1 cell and aren't filling down the formula.

         

        Anyway glad your workbook is working.

Resources