FILTER Function with "Select All"

[BrittKnee]

Hi All! I currently am using the FILTER formula to allow the user to select their department from a drop down list which then populates a cell based on specific criteria. What my users are requesting is to allow the Chief to make multiple selections with the filter. For example, John Doe is the Chief of A and A consists of Departments A1, A2, and A3. I need to be able to allow the Chief to select all or multiple departments. Currently I am using this formula which allows the department heads and/or Chief to see each department:

=IFERROR(TEXTJOIN(","&CHAR(10),TRUE, SORT(FILTER(talent[Diversity Designation-Name],ISNUMBER(MATCH(talent[Band Level],Band,0))*(talent[9-Box designation]='Data Entry'!$AF$3)*(talent[Department]=$J$3)),1,-1)),"")

 

[Fluff]

If you add "All" to the dropdown, then you could use

=IFERROR(TEXTJOIN(","&CHAR(10),TRUE, SORT(FILTER(talent[Diversity Designation-Name],ISNUMBER(MATCH(talent[Band Level],Band,0))*(talent[9-Box designation]='Data Entry'!$AF$3)*(IF($J$3="All",1,talent[Department]=$J$3))),1,-1)),"")

 

[BrittKnee]

This worked. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.