Forum Discussion
Solved
Concatenating arguments in the Filter() function
I've built a formula with filter() that works fine when I type in the range names for the arguments directly =filter(b3:r20,Sheet1!c3:c20=t2,"") And if I replace b3:r20 with the name of the range i...
And that's precisely what I've provided you with. Have you not tested? Choose any cell you like to hold the operator, e.g. W1:
=FILTER(RangeToFilter,COUNTIF(ConditionValue,W1&INDIRECT(VLOOKUP("Q",$T$4:$U$5,2,0))),"")
This was not a legitimate possibility with your formula and the whole point of my switching to a construction involving COUNTIF.
Regards
Hmmn... Thanks for your response - I'm not sure I get it. My problem is not the logic - I'm getting the filter results I want. My question is about how I can swap in different conditional operators without making the user edit the formula. I'm trying to give them a cell where they can pick the operator they want from a drop down and then the Filter formula get's constructed by concatenating their choice into the function's argument...
And that's precisely what I've provided you with. Have you not tested? Choose any cell you like to hold the operator, e.g. W1:
=FILTER(RangeToFilter,COUNTIF(ConditionValue,W1&INDIRECT(VLOOKUP("Q",$T$4:$U$5,2,0))),"")
This was not a legitimate possibility with your formula and the whole point of my switching to a construction involving COUNTIF.
Regards
- Fantastic - yes! I'm not sure why countif and reversing the argument order works (I'll have to think about that some more) but it does so thank you!!!