for demonstration, assume you data starts in cell M10 of Sheet1

first create a dynamic named range so only the populated cells will be in
your list.

Insert=Name=Define
name: List1
refersto: =Offset(Sheet1!$M$10,0,0,countA(Sheet1!$M$10:$M$16 0),1)

Use List1 as the source for your validation dropdown

=List1

in the Change Event of the worksheet (on the sheet tab, right click, select
view code. In the resulting module at the top, in the left dropdown, select
worksheet; in the right dropdown, select Change) do your sort:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Not Intersect(Target, Range("M10:M160")) _
Is Nothing Then
Application.EnableEvents = False
Range("List1").Sort key1:=Range("List1")(1), _
order1:=xlAscending
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Glice" wrote:

I have a moving list which is the source of validation. I estimated that I
would be using 120 items which are not yet all known. When a new item is
entered, I need to re-sort the list alphabetically so that the names that
will appear on the drop down box of the validation would be alphabetized. Is
there any method by which the names in the list and/or in the dropdown box
would be automatically alphabetized as new items are entered in the list?
Anyone, please help me.