May 29 2020 08:41 AM
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 it still works
=filter(RangeToFilter,Sheet1!c3:c20=t2,"")
It also still works if I change the conditional value to a Named Range
=filter(RangeToFilter,Sheet1!c3:c20=ConditionValue,"")
When I change 'Sheet1!c3:c20' to a cell reference containing 'Sheet1!c3:c20' it still works
=filter(RangeToFilter,indirect(vlookup(a lookup to a 2-column range with column ranges listed)=ConditionValue,"")
Where I run into a problem is when I try to use concatenation (the '&' operator) to bring in the conditional operator
=filter(RangeToFilter,indirect(vlookup(a lookup to a 2-column range with column ranges listed)&ConditionOperator&ConditionValue,"")
Is there some way to use the Filter() function so it can construct its arguments by picking up the filtering condition from cells where my user will have selected his/her conditions?
May 29 2020 09:22 AM
Hi,
=FILTER(RangeToFilter,COUNTIF(ConditionValue,">"&INDIRECT(VLOOKUP(your_v_lookup))),"")
Amend the condition (">" here) as required. Note that it needs to be this way round, i.e. the condition precedes the array being queried, so just be careful with your logic: effectively the above is filtering for values which are less than ConditionValue.
Regards
May 29 2020 09:38 AM
May 29 2020 10:52 AM
Solution
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
May 29 2020 03:22 PM
May 29 2020 10:52 AM
Solution
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