I would like to filter one of my columns but would like to select the filtering from another cell.

Have a look at this:

http://i10.photobucket.com/albums/a113/0000o0000/filter.jpg

Instead of having to click on the arrow below CLASS - i want to be able to have something similar (or a combo box type thing) where it says CLASS at the top row.

How can this be achieved?

Thanks

You can write code to hide the rows that where a cell does not match a given value.

Brian

You can write code to hide the rows that where a cell does not match a given value.

Brian

thanks for the advise but i'm bobbins at writing codes

Its getting a bit late here for me to look at a combo but if you had a cell named Filtercell in which you typed your selection then
Public Sub FilterClass()
' BJWARNOCK
Dim myrange As Range
Dim c As Range

Worksheets("sheet1").Activate
Set myrange = Range("$b$2", Range("b65536").End(xlUp))

For Each c In myrange
If c <> Range(Names("filtercell")) Then
c.EntireRow.Hidden = True
End If
Next c

End Sub

Placed in a module will work.

I'll look back in tomorrow.

Brian

The use of Range(Names etc only appears to work in Module code and a command button only appears to be capable of running worksheet code so change the code to


Dim myrange As Range
Dim c As Range

Worksheets("sheet1").Activate
Set myrange = Range("$b$2", Range("b65536").End(xlUp))

For Each c In myrange
If c <> Range("G1") Then ' G1 is where the class required is entered
' obviously you should change this to your requirements
c.EntireRow.Hidden = True
End If
Next c

but first add a command button to your sheet and then right click it , view code from local menu and copy and paste your code into the command buttons Sub.

Brian

Ok after rereading your first post have changed my mind, an old guy's perogative.:D

Open your worksheet select Alt F11 to open the VB editor and paste in the code below
Alter the ranges as required $H$1 is the cell you are going to enter the Class

B2:b6etc is the col and starting cell of the data.

Now everytime you enter anything in the Class selection and hit enter the filter will apply.


Brian

Oh the code!
Private Sub Worksheet_Change(ByVal target As Range)

If target.Address <> "$H$1" Then Exit Sub
Range("a1", "A65536").EntireRow.Hidden = False


' BJWARNOCK
Dim myrange As Range
Dim c As Range

Set myrange = Range("$b$2", Range("b65536").End(xlUp))

For Each c In myrange
'If c <> Range(Names("filtercell")) Then
If c <> Range("h1") Then
c.EntireRow.Hidden = True
End If
Next c
End Sub