How to sort items in dropdown list in Excel 2010?

SAM2009
SAM2009 used Ask the Experts™
on
How to sort items in dropdown list in Excel 2010?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you mean a data validation dropdown list or something else?

Author

Commented:
yes you know the arrow and when you click on it, it shows the dropdown list? I just want to sort the content.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Again are you talking about a data validation dropdown list? If so then create a Named Range for the source range (for example MyList) with a "refers To" of something like this: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001)). Then in the Data Validation set "Allow" = List and the "Source" = "=MyList".
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

What part of that does the "sorting", Martin?

Author

Commented:
Martin what you said I know that and already did that to create my dropdown list but as Tel2 said where is the sort part? How can sort the content in the list?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
You're right I didn't include sorting in my answer. Do you have the ability to sort the source range or keep a helper column that has the data sorted?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You cannot sort data validation list on its own, unless the the source list is sorted itself.
To do that, you may insert a helper column to have the sorted list of your source list and then use this helper column as a source list in the data validation.

For example if you original source list is in col. A but its not sorted, you may create a helper col. Say col. AA and populate this column with the sorted list of col. A values with the help of a formula and then use this sorted list in col. AA as a source list in data validation. That's the only way to achieve this if you don't want to use VBA code to add data validation drop down list programmatically.

If this idea appeals you, please upload a sample workbook to know what values you have originally in the source list so that a solution can by suggested efficiently.
Commented:
If it is acceptable to sort the original validation list, then you could just sort the validation list and make sure you manually sort it each time you add something to the validation list.  To make it more usable (and somewhat foolproof) for future additions, you would need to make the list into a table and reference your table for the validation list.  If you do not like the Excel tables, then another work around (although not quite as pretty) is to have a dummy record at the end that is something like "zzzz----------" that would always sort at the end and would be obvious when you are picking from the list that it is not an acceptable choice.  You would then name the range and use the named range in your validation.  When you add records, you would just need to be sure you added them before the "zzz---------" record and manually resort the list.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial