Data Validation Using Lists: help user to find values starting with acharacter (like auto-filter)

[Chrisso]

Hi All

I have cell validation using a list. The list is very long as it lists
all the staff in the company.

When my users use the pull down they have to scroll all the way down
the list to find the name they are seeking. Excel provides no
assitance in "jumping" to all the names that start with a particular
letter. For example, it would be very useful if I am looking for
"Smith, Peter." to be able to hit the letter "S" and have Excel "jump"
me to all the names starting with "S".

This feature is in Excel for auto-filters but not for data validation
based on lists (as far as I can see).

Does anyone know of any tricks or configuration that would help me
achieve this end. My system would be much easier to use if I could
provide this help to my users.

I am using Excel 2003.

Thanks for any ideas in advance,
Chrisso

 

[Chrisso]

Looks like the solution is here:

http://www.contextures.com/xlDataVal10.html

 

[Don Guillett]

I have this line in a worksheet_change event so if I put in d in cell c1 I'm
taken to the first word with d as the first letter. If I put in co I'm taken
there.

If Target.Address = "$C$1" Then Call gotoltr

Here is something you may be able to use
Sub gotoltr()
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = UCase(Range("c1"))
'MsgBox x
ml = Len(x)
'MsgBox ml
For Each c In Range("a5:a" & lr)
'MsgBox Left(c, ml)
If Left(Trim(UCase(c.Value)), ml) = x Then
' MsgBox c.Row
Exit For
End If
Next
Cells(c.Row, 1).Select
End Sub

 

[L. Howard Kittle]

Hi Chrisso,

Don has offered a VBA solution that I am sure is solid gold, he is a pro.

Here is a worksheet solution, and I don't know who to credit it to.

Sort your staff list and at the beginning of the A's enter a single A. Do
the same for the B's, C's and on to the Z's.

Now in the cell that has the DV, type in the beginning letter of the staff
member's name and DO NOT hit enter. Leave the cell in edit mode.

Now click the down arrow of the cell and you will be at the top of that
letter's list.

HTH
Regards,
Howard

 

[Chrisso]

Thanks Don - this works as you describe.

However I am not sure how this helps me if C1 has list validaiton in
effect.

Have I missed something?

Chrisso

 

[Chrisso]

Thanks Howard - that is certainly one way to help the user. Simple and
useful altho I now have to prevent a *stupid* user selecting one of
the letters instead of a name which I guess I can do with a change
event.

Pity I cannot solve the other problem that 8 rows is not nearly enough
really.

Thanks a lot tho.

It seems the mot udeful hack for my users is

http://www.contextures.com/xlDataVal10.html

Although I am sure that this will fall apart somehow.

Sigh.....

Chrisso

 

[Don Guillett]

Works just fine using cell c1 data validation list. I just tested this in a
file.
If you still can't get it feel free to send your workbook to my address
below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
If Target.Column = 3 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
End If
end sub