Chandoo Filtering LIsts on Multiple Criteria

[Brian Gould]

I want to modify the Chandoo Advanced Filter Example (advanced-filter-example.xlsx ) where instead of two criteria I want only one criteria based on the value of a cell outside the table. Below is the set up and the advanced filter settings. What is wrong?

 

[Hui]

The criteria Range should be
$B$4:$D$5
or
$C$4:$C$5

 

[NARAYANK991]

Hi Brian ,

To add to what Hui has posted :

1. The criteria range has to be at least 2 cells.

2. These two cells ( or more , if there are more criteria ) , have to be contiguous cells ; you cannot have a criteria range such as $C$3 , $C$5.

3. These two cells ( or more , if there are more criteria ) , have to be vertically oriented ; you cannot have a criteria range such as $C$3:$D$3.

4. The top row in the criteria range has to have the field header , and this has to match the header present in the table / range containing the data. Thus , if C4 contained the header Fruits , the Advanced Filter would not return any results.

4. The criteria themselves can be specified in several different ways ; if your criteria range is $C$4:$C$5 :

• In cell C5 , you can enter the text Bananas.
• you can enter the formula ="Bananas" , where the text is Bananas is enclosed in double quotes to signify that it is a text string.
• you can enter the formula =Bananas , where Bananas would be a named range , containing the text Bananas.

5. The criteria can use the wildcards such as ? and * ; thus , if you wish to look for the substring an , then in C5 , enter ="*an*" ; this will return cells containing both Bananas and those containing Oranges.

Entering the formula ="??an*" in C5 will return only Oranges.

Narayan