I have this formula (simplified because it is long):


In place of range can I refer to the cell AC2 that would contain the range name?

I have two ranges, Range1 & Range2. I want execute different Filters depending which range a user selects from the dropdown.

enter image description here

Something like this...

=IFS(AC2="Range1", filter_function1, 
     AC2="Range2", filter_function2)`

This is because I want the filtering range to change with the users' selection. I tried just referring to that cell K2, but got an error.

Then, I put an IF condition instead of range in the filter criteria, but nothing worked.

1 Answer 1


We can use the INDIRECT function to turn a string into a reference like so,

=FILTER(INDIRECT("range1"), INDIRECT("range1") <> "")

But this function does have a flaw that you have to change the values in two different places, and as the filter grows, it becomes even more cumbersome to update. We can utilize the new function, LET.

=LET(name,INDIRECT("range1"), FILTER(name,name <> ""))

---Learn more about LET here.---

you can also use the LAMBDA function if you want. But it is older and a bit clunky looking.

=LAMBDA(name,FILTER(name, name <> ""))((INDIRECT("range1")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.