Sorting Validation Lists

[fkwilliams@gmail.com]

Hi Guys


I have created dynamic a list with Resource names that I am using in a Validation formula. The list has to be in a specific order- (grouped), hence the names are not in alphabetical order in the original list. When they appear in the Drp-Down list they are not in alphabetical order either, hence a bit untidy and not easy to search.

Can anyone help with how I can make the list appear in alaphabetical order despite original list not beeing.


Many thanks in advance

 

[vasim]

=INDEX($A$1:$A$5,MATCH(SMALL(COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),ROW(1:1)),COUNTIF($A$1:$A$5,"<"&$A$1:$A$5),0))


Enter this as an array formula.....Shift+Ctrl+Enter...


Change range $A$1:$A$5 as required...

 

[fkwilliams@gmail.com]

Hi Indian.


Sorry, where do i enter this formula. Is this in place of the Validation formula?

Also what does SMALL refer to?

 

[vasim]

Assuming your range is in A1:A5 you will enter this formula in B1:B5 and then give validation on B column.....

 

[anupagarwal06]

Hi F K Williams,


I have done this once in an attendance record keeping sheet. But this will require at least 3 more columns in your work sheet. That too it generated a pseudo alphabetical order and hand many caveats.


But there is an awesome way already put up by the hero. Here is the link:

http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Enjoy!

 

[fkwilliams@gmail.com]

Brilliant guys, it works beautifully.


Thanks to both and our hero...