sorting a dropdown list automatically
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 |
sorting a dropdown list automatically
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Mohammed" wrote: 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 |
sorting a dropdown list automatically
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mohammed" wrote: 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 |
sorting a dropdown list automatically
The DV dropdown will display the required auto-ascending
sort of the source data in Sheet1's col A. The DV dropdown list will display neatly only the full range of the sorted numbers, w/o any blanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
All times are GMT +1. The time now is 09:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com