Alphabetical arrange my drop down list

[sandeepafpl]

Dear Forum,

I have arranged some name of ledgers through data validation. Now, when i am adding new ledgers in the list, the new ledgers comes at the end of drop down list. Is it possible to arrange the drop down list alphabetical without sorting data validation list.


Regards,
Sandeep,
Pune.

 

[Zakkaroo]

Perhaps there is a better solution, but if sorting the data is not an option, you may need to create a 2nd, automatically updated list, based on the first one - that also automatically sorts alphabetically.

See example given in the link below

Create a drop down list containing alphabetically sorted values in excel | Get Digital Help - Microsoft Excel resource

Essentially, your original list stays where it is, completely unsorted, and through the use of formulas and dynamic named ranges, you get a 2nd list that IS sorted alphabetically.

For the sake of 'tidyness' you could hide the 2nd, sorted list away somewhere.

 

[sandeepafpl]

Done,


Thanks Zakkaroo..

 

[Aladin Akyurek]

Done,


Thanks Zakkaroo..

Replace the ROW(A1) bit in the formula the link proposes:

=IF(COUNTA(List)>=ROWS($A$2:A2), 
  INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), 
  COUNTIF(List, "<"&List), 0)), "")

with ROWS($A$2:A2).