Dynamic Filters When List Includes "All"

shellp

Board Regular
Joined
Jul 7, 2010
Messages
207
Office Version
  1. 365
  2. 2021
  3. 2010
  4. 2007
Platform
  1. Windows
Hello,

I am using Excel 365. I've created a list used in data validation that includes "All" as an option
Excel Formula:
VStack("All",TOCOL(Sort(Unique(raw[Fiscal_Year]),1,1))

If the user leaves it as "All" then the filter syntax will be
Excel Formula:
<>""
vs
Excel Formula:
=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.
 
Something like
Excel Formula:
=IF(F6="All",filter_range,FILTER(filter_range,criteria_range=F6))
 
Upvote 0
Thanks Cubist. What I want to do is include the criteria in a filter statement:
Excel Formula:
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?
 
Upvote 0
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.
 
Upvote 0
It'd be easier and less back and forth if you share a representative example of your data, and your expected output.
 
Upvote 0

Forum statistics

Threads
1,226,876
Messages
6,193,461
Members
453,801
Latest member
777nycole

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top