Hope I got the thread title relevant to what I am about to ask,
Currently I have a table that has been filtered to only showing 3 columns of my choosing.
In G5 & H5 is the criteria range for ColumnA.
G10 & H10 is the criteria range for ColumnC.
The formula below works exactly that.
I want to take it a step further.... Cell K1 has a data validation drop down list to pick between ColumnA and ColumnB while cell L1 has a data validation drop down list between ColumnC and ColumnD.
The million dollar question is, how could I get G5 & H5 or G10 & H10 to recalculate based on which header is in K1 or L1? For example, If I choose ColumnB from the drop down list in K1, the input value from G5 & H6 (>=4 & <=75) should now look in ColumnB for the calculation and not ColumnA anymore, same goes if the drop down list in L1 is ColumnD, G10 & H10 would look in ColumnD to return the values based on G10 & H10 criteria range.
If this makes any sense, my complete gratitude in advance if anyone can help update this formula accordingly. Thank you
Currently I have a table that has been filtered to only showing 3 columns of my choosing.
In G5 & H5 is the criteria range for ColumnA.
G10 & H10 is the criteria range for ColumnC.
The formula below works exactly that.
I want to take it a step further.... Cell K1 has a data validation drop down list to pick between ColumnA and ColumnB while cell L1 has a data validation drop down list between ColumnC and ColumnD.
The million dollar question is, how could I get G5 & H5 or G10 & H10 to recalculate based on which header is in K1 or L1? For example, If I choose ColumnB from the drop down list in K1, the input value from G5 & H6 (>=4 & <=75) should now look in ColumnB for the calculation and not ColumnA anymore, same goes if the drop down list in L1 is ColumnD, G10 & H10 would look in ColumnD to return the values based on G10 & H10 criteria range.
If this makes any sense, my complete gratitude in advance if anyone can help update this formula accordingly. Thank you
| DataSample.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | ColumnA | ColumnB | ColumnC | ColumnD | Category | Category | ColumnA | ColumnC | ||||||
| 2 | 11 | 38 | 51 | 145 | 1 | Column A or B | 1 | 11 | 51 | |||||
| 3 | 6 | 64 | 23 | 357 | 1 | Greater or Equal | Less or Equal | 1 | 6 | 23 | ||||
| 4 | 53 | 15 | 1 | 754 | 1 | 1 | 53 | 1 | ||||||
| 5 | 3 | 99 | 9 | 423 | 1 | 4 | 75 | 1 | 75 | 53 | ||||
| 6 | 75 | 75 | 53 | 485 | 1 | 1 | 35 | 278 | ||||||
| 7 | 35 | 5 | 278 | 987 | 1 | Column C or D | 1 | 19 | 111 | |||||
| 8 | 19 | 10 | 111 | 564 | 1 | Greater or Equal | Less or Equal | 1 | 7 | 60 | ||||
| 9 | 89 | 22 | 325 | 242 | 1 | 1 | 54 | 90 | ||||||
| 10 | 7 | 91 | 60 | 127 | 1 | |||||||||
| 11 | 54 | 28 | 90 | 854 | 1 | |||||||||
Sheet1 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2:L9 | J2 | =LET(A_,Table4[ColumnA],C_,Table4[ColumnC],Category_,Table4[Category], IFERROR(CHOOSECOLS(FILTER(Table4,ISNUMBER( 1/IF(G5,A_>=G5,Category_) *1/IF(H5,A_<=H5,Category_) *1/IF(G10,C_>=G10,Category_) *1/IF(H10,C_<=H10,Category_)) =TRUE),XMATCH(J1:L1,Table4[#Headers])),"")) |
| Dynamic array formulas. | ||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| K1 | List | =$A$1:$B$1 |
| L1 | List | =$C$1:$D$1 |
