Filter by typed value

[magmike]

In a sheet with 45,000 rows I would like the user to be able to filter out extraneous rows by typing the desired value in the row above the data.

If they type "TX" in the row above the state column, only the rows with "TX" show.
If they type "Dallas" in the row above the city column, only the rows with "Dallas" show.
If they type "TX" in a row above the state column, AND they type "Dallas" in the row above the city column, then only rows with "TX" in the state column AND "Dallas" city column will show.

Is there an easy way to do this with a formula or VB?

Thanks in advance for your help,
magmike

 

[Claus Busch]

Hi Mike,

Am Tue, 30 Jul 2013 18:07:44 -0700 (PDT) schrieb magmike:

If they type "TX" in the row above the state column, only the rows with "TX" show.
If they type "Dallas" in the row above the city column, only the rows with "Dallas" show.
If they type "TX" in a row above the state column, AND they type "Dallas" in the row above the city column, then only rows with "TX" in the state column AND "Dallas" city column will show.

your state column is A and the city column is B (else modify to suit)
Try this in the code module of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:B1")) Is Nothing _
Then Exit Sub
Dim myRange As Range

'Set the range of your table
Set myRange = Range("A2:E100")
If Target <> "" Then
myRange.AutoFilter field:=Target.Column, Criteria1:=Target
Else
myRange.AutoFilter field:=Target.Column, Criteria1:="<>"
End If
End Sub


Regards
Claus B.

 

[magmike]

In a sheet with 45,000 rows I would like the user to be able to filter out extraneous rows by typing the desired value in the row above the data. Ifthey type "TX" in the row above the state column, only the rows with "TX" show. If they type "Dallas" in the row above the city column, only the rowswith "Dallas" show. If they type "TX" in a row above the state column, ANDthey type "Dallas" in the row above the city column, then only rows with "TX" in the state column AND "Dallas" city column will show. Is there an easy way to do this with a formula or VB? Thanks in advance for your help, magmike

It doesn't work. I am emailing you a screen shot.

 

[Claus Busch]

Hi Mike,

Am Wed, 31 Jul 2013 05:23:03 -0700 (PDT) schrieb magmike:

It doesn't work. I am emailing you a screen shot.

I answered by email. Workbook is attached.


Regards
Claus B.

 

[magmike]

Hi Mike, Am Wed, 31 Jul 2013 05:23:03 -0700 (PDT) schrieb magmike: > It doesn't work. I am emailing you a screen shot. I answered by email. Workbookis attached. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

What – you mean, if I tuck it away in a module and never called out, it won't work? ;-)

Thanks for the tip!

magmike

 

[magmike]

This works great when the file is stored locally. But, now i am trying use it through an app called CloudOn, and apparently it doesn't support macros.Do you know of an iPad app that allows you to use macro functionality of excel files, or is there a way this could be done with formulas?

Magmike