I'm looking to create a filter with two dropdown lists with All options for both lists. Currently I can figure out how to get two dropdown lists to work, and how to make an all function with one dropdown list, but not with two lists.

The link to the sheet is here

https://docs.google.com/spreadsheets/d/1dfj60oEmm5w6W9ZoRQ2dpXqFMs1IOrtXulXi0qoG6kA/edit?usp=sharing

The code without the "All" function is below

=sort(FILTER('List of Classes, Divided by Classes'!A:P,IF(B1,('List of Classes, Divided by Classes'!$B:$B=B1),) IF(C1,('List of Classes, Divided by Classes'!$C:$C=1),) IF(D1,('List of Classes, Divided by Classes'!$D:$D=D1),) IF(E1,('List of Classes, Divided by Classes'!$E:$E=E1),) IF(F1,('List of Classes, Divided by Classes'!$F:$F=F1),) IF(G1,('List of Classes, Divided by Classes'!$G:$G=G1),),'List of Classes, Divided by Classes'!P:P=I1,'List of Classes, Divided by Classes'!M:M=K1),1,TRUE)

CodePudding user response：

I've added a new sheet ("Erik Help") with your formula modified as follows:

=ArrayFormula(SORT(FILTER('List of Classes, Divided by Classes'!A:P,IF(B1,('List of Classes, Divided by Classes'!$B:$B=B1),) IF(C1,('List of Classes, Divided by Classes'!$C:$C=1),) IF(D1,('List of Classes, Divided by Classes'!$D:$D=D1),) IF(E1,('List of Classes, Divided by Classes'!$E:$E=E1),) IF(F1,('List of Classes, Divided by Classes'!$F:$F=F1),) IF(G1,('List of Classes, Divided by Classes'!$G:$G=G1),),IF((I1="") (I1="All"),ROW('List of Classes, Divided by Classes'!A:A),'List of Classes, Divided by Classes'!P:P=I1),IF((K1="") (K1="All"),ROW('List of Classes, Divided by Classes'!A:A),'List of Classes, Divided by Classes'!M:M=K1)),1,TRUE))

I wrote it such that either "All" or a blank cell in I1 or K1 will result in all matches according to checked days.

The IF clauses in both cases simply say that if the value of I1 or K1, respectively, is "All" or blank, to return all row numbers as the Boolean value. Since any number other than 0 is TRUE, you get the effect of "everything" for that IF check.