Excel2007:Advanced Filter

Overview
Excel allow you to extract data from a table based on a filter you create. In this example rows containing a specified Manufacturer are extracted and placed in another part of the spreadsheet. You need to know how to name ranges to do this.

Creating an Advanced Filter


Example :.

A simple named cell
This short video explains how to create an advanced filter.



What we are going to do is to find records in a list (we will call our database), based on a filter, and place them onto the same sheet below the filter.

.. and thats all!!
 * 1) Create the spreadsheet as shown (it is the same one used for the Named reference)
 * 2) Highlight the database (A6:H11) note that this includes the table headings. Name this database
 * 3) Create the filter by
 * 4) Copy the database headings (row 6 (A6:H6)) to row 13 - use [Control C to copy > Control V to paste.
 * 5) In the row below the filter heading type in the criteria, here enter Samsung to find all the Samsung drives (see notes below for other options)
 * 6) Name the filter by
 * 7) Highlight the filter heading and criteria (A13:H14) and name as filter
 * 8) Display results starting in Row 16
 * 9) Click in A16
 * 10) Select the Data tab, then in the Sort&Filter group choose Advanced
 * 11) With the list range box highlighted, click F3 to get the named ranges and choose database, then [Ok]
 * 12) Click on the Criteria Range box, then [F3] and choose criteria, then [Ok]
 * 13) In the Action area on the radio button to Copy to another location
 * 14) Click in the Copy to: box, then click on the button at the right, a small dialog box appears.
 * 15) Click on A16 and this should appear in the box.
 * 16) Click on the button on the right of the text box and you should return back to the Advanced filter dialog box
 * 17) Click [Ok] to apply the filter