ExcelBanter - 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