Advanced Filter

Hi Leila

Here's the instructions from the Excel 2007 Intermediate Handbook.

To create an advanced filter:
 Select any cell in the main data table.
 Select the Data ribbon, from the Sort and Filter group click the Advanced button.
 If you wish to filter within the current database, ensure that the Filter the list, in-place option is selected in the Action area.
 If you wish to create a copy of the filter results to be displayed separately from the table, choose the Copy to Another Location option and click an empty cell with sufficient space below and to the right to allow for the placement of the filtered data. The location must be located on the same sheet as the data table.
 In the List Range box, select or enter the cell references for the table.
 In the Criteria Range box, select or enter the cell references for the criteria range.
 To ensure that only one record is displayed, if some contain exactly the same data, turn on the Unique Records Only check box.
 Click OK. Data in rows not matching the criteria set will be hidden.

To display the full table again, click the Clear button in the Sort & Filter group on the Data Ribbon.


In summary, you set up a criteria range with the same field headings as in the database table. Then type in criteria range below the headings what you are filtering, for example

Age Height
<40 <60

Then with your cursor in the database choose:
Data, Advanced (from Sort & Filter group)
Fill in the List range (database including headings), criteria range (including headings) and press OK.
Choose Copy to if you want the result in a separate range.

Please let me know if it worked. I can send you a working example if that helps.

Regards
Doug Dunn
Best STL