So I have a cell C4 in which I have an item list for a data validation. Below, I want to create a filter that will take all the entries in another sheet named All and compare that to the C4 cell.
So far I have this:
=Filter(All!A2:U999, All!C2:C999 = C4)
And it works. However, I also want to add an option to ignore the C4 cell. So, in my data sheet I can have the values of A,B,C,D or E, and by using the filter method above, I can successfully filter out all entries of the same type. But in the C4 cell I have an extra value: "Any".
How can I modify the filter formula so that when the "Any" filter is selected, the filter shows me all the entries from my data sheet?
In laymans terms, I want something like this.
IF(C4 <> "Any") THEN
Only show entries that are equal to C4
ELSE
Show all entries
Keep in mind that I want to do this multiple times for the same filter, with more C4-like cells for a more accurate search, so the formula must be expandable.