Need a drop-down field that automatically sorts ascending

[ScottFisher2004]

I'm looking for a way to include a drop-down field in a form that
automatically sorts the source values ascending. Sorting the source data is
not a possibility.

I'm using Excel 2003. I have a 'master' worksheet that pulls customer data
from numerous separate data entry Excel files via links. I have a 'customer
summary' worksheet in the same workbook as the master worksheet is in. The
'customer summary' worksheet pulls information for a specific customer from
the 'master' worksheet into a single-customer view.

Currently, I'm using data validation in the 'customer summary' sheet to
select a customer. The drop-down isn't sorted alpha so it's difficult to
find a specific customer. Then, I use VLOOKUP to pull over the data for that
customer into the 'customer summary' sheet.

In addition to data validation, I tried a combo box but couldn't get that to
sort alphabetically either.

Any thoughts / solutions would be greatly appreciated!

FYI - If your solution is to do something via code, please provide a lot of
detail / instruction.

 

[Otto Moehrbach]

If I understand you correctly, I would use a Worksheet_SelectionChange event
macro to fire whenever the Data Validation cell is selected. Mind you, that
macro will fire when that cell is selected, not when a selection is made
from the drop-down list. Once fired, that macro will copy the entire column
of customer names, paste it to some out-of-the-way place in the workbook,
values only, sort that list as needed, and name that list as needed so that
it will work with the Data Validation. Then when the user clicks on the
down-arrow of the DV cell, he will see a sorted list. Does this sound like
what you want? HTH Otto

 

[Otto Moehrbach]

Something like this perhaps. I assumed that the DV cell is A1, the headers
are in row 2, and the names are in Column A starting in row 3. Post back if
you need more. HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RngToSort As Range
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Sheets("Utility")
.Columns("A:A").ClearContents
Range("A3", Range("A" & Rows.Count).End(xlUp)).Copy
.Range("A1").PasteSpecial xlPasteValues
Set RngToSort = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
RngToSort.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
HEADER:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
RngToSort.Name = "TheNames"
Range("A1").Select
End With
End If
End Sub