Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lists and the VLOOKUP function In Excel 2003
I need to be able to allow users to type a last name in a column (A) that I
have setup as a list feature. Currently the only option is to scroll through the entire list, I'd rather allow them to begin typing a last name and have the list go to the last name as they type. Can this be done in a list? For instance, in cell A2 they find the last name (by scrolling the list) then select the last name and by using a VLOOKUP function it places the first name (cell B2), MI (cell C2) and ID# in (cell D2). Thanks, Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Lists and the VLOOKUP function In Excel 2003
Here is one I did for a client recently for a number in a1 or text in h1
Private Sub Worksheet_Change(ByVal Target As Range) Dim x As String If Target.Column = 1 And Target.Row = 1 Then lr = Cells(Rows.Count, 1).End(xlUp).Row Range("Sort_Area").Sort Key1:=Cells(1, Target.Column), _ Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Cells(3, Target.Column).Select x = Cells(1, Target.Column) 'MsgBox x ml = Len(x) 'MsgBox ml For Each c In Range("a3:a" & lr) 'MsgBox Left(c, ml) If Left(c.Value, ml) = x Then ' MsgBox c.Row Exit For End If Next Application.Goto Cells(c.Row, 1), scroll:=True 'ActiveCell.Offset(, 7).Select End If If Target.Column = 8 And Target.Row = 1 Then lr = Cells(Rows.Count, 1).End(xlUp).Row Range("Sort_Area").Sort Key1:=Cells(3, Target.Column), _ Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Cells(3, Target.Column).Select x = UCase(Cells(1, Target.Column)) 'MsgBox x ml = Len(x) 'MsgBox ml For Each c In Range("h3:h" & lr) 'MsgBox Left(c, ml) If Left(Trim(UCase(c.Value)), ml) = x Then ' MsgBox c.Row Exit For End If Next Application.Goto Cells(c.Row, 1), scroll:=True ActiveCell.Offset(, 7).Select End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "watermt" wrote in message ... I need to be able to allow users to type a last name in a column (A) that I have setup as a list feature. Currently the only option is to scroll through the entire list, I'd rather allow them to begin typing a last name and have the list go to the last name as they type. Can this be done in a list? For instance, in cell A2 they find the last name (by scrolling the list) then select the last name and by using a VLOOKUP function it places the first name (cell B2), MI (cell C2) and ID# in (cell D2). Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why would a vlookup function stop working in excel 2003? | Excel Worksheet Functions | |||
Lists in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 "VLOOKUP" function | Excel Worksheet Functions | |||
Vlookup function 2003 | Excel Worksheet Functions | |||
Excel 2003 Lists Vs Database | Excel Discussion (Misc queries) |