List management - sorting

Hello Terri,

Thank you for your question. Yes, it is frustrating that Data Validation has no option built in to sort the drop down for you when the source data can't be sorted.

The first solution I could suggest is to place a copy of the original list on another sheet and then sort that list. You can use the new sorted list to populate the drop downs.

If records are added to the original data, replace the drop down's source list to include the new items. Of course you are going to have to sort the list again.

Afterwards, you also need to expand the range of the drop down list in the Data Validation box to include the new items.

I realise that it seems cumbersome and I tried recording these steps in a Macro, but I find it doesn't record the sorting part, which is the whole point of your question.

Secondly, if you are up for two longish formulas and two named ranges, I have found a brilliant web page with a different solution using what is a dynamic named range and dynamic sorting.

I have tried it and it works very well. Follow the steps precisely on some sample data first, to check it out, then you could try it on a copy of your data. Here is the link:

http://www.extendoffice.com/documents/excel/2652-excel-drop-down-list-alphabetical-order.html

On your data, adapt the cell references in the formulas to reflect where your data is.

Good luck!

Marius Barnard
Excel Trainer