sorting a dropdown list automatically

M

Mohammed

I can do a dropdown list for the whole column of data but i need when i press
on the dropdown list to see the data in sorted way because the column where i
am taking the dropdown list from is editable column
Best regards
 
M

Martin Fishlock

Mohammed,

There isn't an action for the before entry to a drop down list.

A possible solution is to sort the data entry column after a change, her
I've assumed it was column A.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Me.Range("A:A"), Target) Is Nothing) Then
' need to modify to meet your requirments
Me.Range("A:A").Sort Key1:=Me.Range("A1")
End If
End Sub

You need to modify the sort to meet your requirments.
 
M

Max

Assuming source data is in A1 down in Sheet1, with data comprising unique
numbers only with possible blank cells or formula cells returning null
strings in-between, here's one way ..

Put in B1: =IF(A1="","",A1)
Put in C1: =IF(ROW()>COUNT(B:B),"",SMALL(B:B,ROW()))

Select B1:C1, copy down to cover the max extent of data in col A.
Col C will return an auto-ascending sort of the numbers in col A.

Click Insert > Name > Define,
enter under
Names in workbook: MyList
Refers to:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--(Sheet1!$C$1:$C$1000<>"")))
Click OK

Note: Adjust the sumproduct range above to suit the max expected extent in
col C. Use the smallest range large enough to cover.

Now in any sheet of the book, we can create DV lists using MyList as the
source for the data validation. Test it out. In any sheet, select the desired
DV range, then click Data > Validation. Under Allow, choose: List. In the
"Source" box, put: =MyList. Click OK. The DV dropdown will display the
required auto-ascending sort of the source data in Sheet1's col A.
 
M

Max

The DV dropdown will display the required auto-ascending
sort of the source data in Sheet1's col A.
Click to expand...

The DV dropdown list will display neatly only the full range of the sorted
numbers, w/o any blanks
 
Top