Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown list connected to another workbook | Excel Worksheet Functions | |||
dropdown list | Excel Discussion (Misc queries) | |||
Can I import a folder's contents as a dropdown list? | Excel Worksheet Functions | |||
Automatically sorting data | Excel Discussion (Misc queries) | |||
How to show dropdown list from another workbook without running it | Excel Discussion (Misc queries) |