Advanced filter criteria; validation lists

  • Thread starter Thread starter Pete Merenda
  • Start date Start date
P

Pete Merenda

I have a data list with roughly 50 columns, which users
will filter according to 5 key columns. Instead of using
auto filter, requiring users to scroll very far to the
right, I've built a "criteria form" for users to select
from validation pulldowns -- all in column A, neat and
easy -- where list values then populate to a hidden
criteria section for advanced filtering. But I can't seem
to figure out how to design the advanced filter criteria
to select all items in the validation list. In other
words, replicating the (All) selection as it appears in
auto filtered lists.

I can write a formula to leave the criteria cell blank (=IF
(d$32="All Products"," ")), but that actually instructs
the filter to look for blank records instead of all
records in that column -- like a cell that is truly blank.

Any idea how to design a validation list criteria that
accounts for all the items in the list?
 
The following formula would allow all records to pass through the
filter, if "All Products" is selected:

=IF(D$32="All Products","",D$32)

Note --Your formula has a space between the quotation marks, and the
formula above has no space.
 
Back
Top