Filter a column of data by typing the user's initials in a c

[Cartmann]

I'm looking for a way to achieve the same results as selecting a value from an Auto-Filter, but I'd like to do it by having the user simply type in the value in a cell instead of selecting it from a list.

Example: I have a list of 13000 accounts, each of which is assigned to 1 of 8 possible sales people - their initials are populated in each row - in column A. I'd like them to be able to type in their initials in cell 'A1' and have the entire report filter itself to show only the accounts to which they are assigned.

 

[Aladin Akyurek]

On 2002-10-07 18:25, Cartmann wrote:
I'm looking for a way to achieve the same results as selecting a value from an Auto-Filter, but I'd like to do it by having the user simply type in the value in a cell instead of selecting it from a list.

Example: I have a list of 13000 accounts, each of which is assigned to 1 of 8 possible sales people - their initials are populated in each row - in column A. I'd like them to be able to type in their initials in cell 'A1' and have the entire report filter itself to show only the accounts to which they are assigned.

What is the exact data range including labels?

 

[Mark W.]

Sounds like an Advanced AutoFilter. See the Excel topics for...

- "Filter a list by using advanced criteria"
- "Examples of advanced filter criteria"

 

[Yogi Anand]

On 2002-10-07 18:25, Cartmann wrote:
I'm looking for a way to achieve the same results as selecting a value from an Auto-Filter, but I'd like to do it by having the user simply type in the value in a cell instead of selecting it from a list.

Example: I have a list of 13000 accounts, each of which is assigned to 1 of 8 possible sales people - their initials are populated in each row - in column A. I'd like them to be able to type in their initials in cell 'A1' and have the entire report filter itself to show only the accounts to which they are assigned.

Hi cartmann:

Welcome to the Board!

The following procedure will do what you are looking for. In the code below for ListRange use your Named range.

When an initial would be entered in cell A1, the list will extract records matching criteria that have same entry in Field1. Keying in DEL in A1 would display the entire list.

For automating you should enter this code in the Worksheet's VBE Module.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
' 10/7/02 by Yogi Anand -- FilterForValueInA1
'
Range("A1").Select
[ListRange].AutoFilter Field:=1, Criteria1:="" & [A1] & ""
If [A1] = "" Then ActiveSheet.ShowAllData
'
End Sub

Regards!

Yogi