Forum Discussion
How to make changes ranges in an array/formula without manually modifying it each time
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
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.
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.)
