If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel; Can I Filter based on a cell value?
Is it possible to filter a list based on a cell value? What I want to do is for the user to type in a criteria in cell F1 and G1 (Dept and Acct No's) and click a button that will launch a macro that will filter the list and display only entries that have Dept and Acct No's that match those entered in F1 and G1.
I have looked through the help files in Excel and can't find a way to do this. If this is not possible is it possible to set something up so that cells that do not fit the criteria are deleted from the spreadsheet somehow? It is ok if non-matching entries are deleted since this list is created when it opens from a protected master-list. |
#2
|
|||
|
|||
Excel; Can I Filter based on a cell value?
Here is some code. It assumes that you want to filter columns A and B and
that the filter is already set on those columns Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$F$1" Then SetAutofilter Columns("A:A"), .Value, 1 ElseIf .Address = "$G$1" Then SetAutofilter Columns("B:B"), .Value, 2 End If End With ws_exit: Application.EnableEvents = True End Sub '----------------------------------------------------------------- Public Sub SetAutofilter(col, val, fld) '----------------------------------------------------------------- With col If val "" Then .AutoFilter Field:=fld, Criteria1:=val Else .AutoFilter Field:=fld End If End With End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "setoFairfax" wrote in message ... Is it possible to filter a list based on a cell value? What I want to do is for the user to type in a criteria in cell F1 and G1 (Dept and Acct No's) and click a button that will launch a macro that will filter the list and display only entries that have Dept and Acct No's that match those entered in F1 and G1. I have looked through the help files in Excel and can't find a way to do this. If this is not possible is it possible to set something up so that cells that do not fit the criteria are deleted from the spreadsheet somehow? It is ok if non-matching entries are deleted since this list is created when it opens from a protected master-list. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Microsoft Excel - "Edit Cell Comment" abnormality | Dave Peterson | Worksheet Functions | 0 | March 9th, 2004 04:18 AM |
error in excel help. how to select a cell after clicking the Select All button? | Dmitriy Kopnichev | Links and Linking | 3 | December 5th, 2003 10:32 PM |
Howto: Filling in values based upon a particular cell | Eric Tubbs | Worksheet Functions | 6 | December 3rd, 2003 07:24 PM |
F2 key does not allow cell editing in Excel (Office XP Pro) | Katrianne Abuhelewa | Worksheet Functions | 3 | October 18th, 2003 09:07 AM |