sorting a dropdown list automatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

---
 
Back
Top