Filter by typed value

  • Thread starter Thread starter magmike
  • Start date Start date
M

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
 
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.
Click to expand...

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.
 
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
Click to expand...

It doesn't work. I am emailing you a screen shot.
 
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.
Click to expand...

I answered by email. Workbook is attached.


Regards
Claus B.
 
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
Click to expand...

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

Thanks for the tip!

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
 
Back
Top