How to sort items in dropdown list in Excel 2010?

How to sort items in dropdown list in Excel 2010?
LVL 1
SAM2009Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Do you mean a data validation dropdown list or something else?
0
SAM2009Author Commented:
yes you know the arrow and when you click on it, it shows the dropdown list? I just want to sort the content.
0
Martin LissOlder than dirtCommented:
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".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tel2Commented:
What part of that does the "sorting", Martin?
2
SAM2009Author 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?
0
Martin LissOlder than dirtCommented:
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?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
psteffCommented:
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.
0
SAM2009Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.