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

Brass Contributor

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

 

 

9 Replies

 

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

@ILikePi 

 

Holy crap! That worked!!!!!

 

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

 

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

@ILikePi ,

 

I your case you may use AGGREGATE to avoid array formula. And, as variant, if your ranges are always start from the same row (second one) and they are of same height, OFFSET could be used for the reference. If number of columns to offset from column A are parametrised in cells A1 and B1 that could be like

=IFERROR(INDEX(OFFSET($A$1,1,$B$1,250),AGGREGATE(15,6,1/($U$1=OFFSET($A$1,1,$A$1,250))*(ROW($C$2:$C$25)-1),(ROW()-1))),"")

@ILikePi 

Don't let the inadequacies of the user interface put you off the clarity of thought that comes with array formulas.  Virtually no one uses array formulas so it is not surprising that the user interface is clunky and inconvenient (I once suggested that the defaults should be reversed and those who wish to use implicit intersection to destroy arrays should be forced to go through the inconvenience of CSE!)

For this question, I had waded through the sea of direct notation, if finished with

{= IFERROR( INDEX( ValueArray, SMALL( IF( TestArray=Criterion, k ), k ) ), "")}

How one defines ValueArray is a separate issue; it would normally be absolute but could be defined as column relative or movable through the use of OFFSET.

 

What is now possible with dynamic arrays is to reduce the whole formula to

= FILTER( ValueArray, TestArray=Criterion, "Null" )

 

My other observation would be that a formula defined as Named formula always has been protected against the ravages of implicit intersection.  That provides an alternative strategy to hiding array calculations in one of the few functions that work properly with arrays (AGGREGATE, SUMPRODUCT, LOOKUP etc.)

 

@Peter Bartholomew , you may wrap array calculations by AGGREGATE, SUMPRODUCT,... without named ranges, isn't it?

@Sergei Baklan 

I think the use of SUMPRODUCT as a wrapper for array formulas is reasonably well known, though some would deny that they are actually performing an array calculation simply because they didn't need CSE.

 

I find it less well-known that a formula placed into the 'refers to' box of a Name will also be evaluated as an array every time the Name is referenced.  Thus

=SUM( myArrayFormulaByName )

does not need CSE even though it would if the parameter were an explicitly written array formula.

@Peter Bartholomew - yes, agree. Named ranges/formulas in general are not very popular, even tables which in some extent could be considered as dynamic named ranges.

@Sergei Baklan 

I would consider tables as the ideal way of holding the initial input and reference data for a model.  My aim when developing a solution is to capture as much structure as I can within the solution and, also, to link it as tightly as possible to the terminology and assumptions of the application domain.  

That rather contrasts with the normal 'tips and tricks' genre aimed at achieving (bad?) solutions faster.  Speed is good but it should not be the main objective.