Filter records based on the value of a cell

**corix** · 01-08-2010, 12:52 PM

Hi everybody,

I have an excel file that gets records from access database. I have a field named "Class" in column C which starts at row 4. I want to filter the records in such a way that only those records with Class equal to whatever value I put in cell B2 will be shown.

For example:

Code:

A             B            C

REPORT

Filter Class: _______

ProjID        Name         Class

001           Project A    4

002           Project B    4

003           Project C    4

004           Project D    8

005           Project E    8

006           Project F    6

007           Project G    6

008           Project H    6

009           Project I    7

010           Project J    10

011           Project K    3

012           Project L    10

Result:

Code:

A             B            C

REPORT

Filter Class: 6______

ProjID        Name         Class

006           Project F    6

007           Project G    6

008           Project H    6

Is this possible at all in Excel? Any help will be greatly appreciated. Thanks.

- corix

**Richard Buttrey** · 01-08-2010, 01:17 PM

Hi,

Only by resorting to a macro.
Create a name for your A3:C... data range - say "Data" and then run the following Macro

Code:

Sub FilterData

Range("Data").AutoFilter Field:=3 Criteria1:=ThisWorkbook.Worksheets("yoursheetname").Range("B2")

End Sub

HTH

**corix** · 01-08-2010, 01:24 PM

Thanks Richard!
I'm not familiar with macros but I'll give it a try.
I really appreciate it.

**corix** · 01-08-2010, 01:42 PM

Another question Richard when you say "Create a name for your A3:C... data range - say "Data"...." I'm not completely sure how I would proceed doing that. I'm sorry I'm really not an expert when it comes to excel.

**Richard Buttrey** · 01-08-2010, 01:50 PM

Originally Posted by corix

Another question Richard when you say "Create a name for your A3:C... data range - say "Data"...." I'm not completely sure how I would proceed doing that. I'm sorry I'm really not an expert when it comes to excel.

Hi,

It's really good practice to get into the habit of using named ranges in all your formulae. It makes them much easier to understand and it's so much more flexible.

There are several ways to create a range name.
If you're using Excel 2007, from the ribbon choose Formulas, then in the Defined Names tab select Name Manager, choose new, enter a name and click the drop down 'Refers To' box and select a range.

Alternatively just highlight the range of cells by dragging your cursor across them, then enter a name in tbe small box that you see to the left of the formula bar just above the letter 'A' of column A.

HTH