Dynamic Filters When List Includes "All"

[shellp]

Hello,

I am using Excel 365. I've created a list used in data validation that includes "All" as an option

VStack("All",TOCOL(Sort(Unique(raw[Fiscal_Year]),1,1))

If the user leaves it as "All" then the filter syntax will be

<>""

vs

=F6

if a value is selected.

How can I dynamically change the filter based on user's selection? I've tried creating a formula in another cell of "if All then <>"" else = F6" and referencing it as indirect() in the filter formula but that isn't working. Also, how do I reference it in the filter itself i.e. fieldname& filter syntax? Or with a comma and the filter syntax?

Any and all assistance greatly appreciated. Thank you so much.

 

[Cubist]

Something like

=IF(F6="All",filter_range,FILTER(filter_range,criteria_range=F6))

 

[shellp]

Thanks Cubist. What I want to do is include the criteria in a filter statement:

Filter(raw,(raw[Fiscal Year]=Fyear

where Fyear is the named range for the filter being used. For this reason, your code doesn't work for my needs because it is producing a spill error. Hopefully this makes sense?

 

[Cubist]

spill error

You have cells that are blocking the formula. Clear everything below and to the right.

 

[shellp]

Got it, this works. BUT I have multiple filters that could be All or specific value and I was hoping the filter statement could be built on the fly i.e. use the If statement to create the syntax that is used depending on the selection: Filter(raw, (raw[Fiscal Year]=dynamic statement)*(raw[agegroup]=dynamic statement) etc.

If I used the approach above for all other filters, can I concatenate them together? Thanks so much for your assistance.

 

[Cubist]

It'd be easier and less back and forth if you share a representative example of your data, and your expected output.