Alphabetical arrange my drop down list

sandeepafpl

Board Regular
Joined
Aug 11, 2012
Messages
120
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

Active Member
Joined
Jul 6, 2009
Messages
382
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,119
Done,


Thanks Zakkaroo..
Replace the ROW(A1) bit in the formula the link proposes:
Rich (BB code):
=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).
 

Forum statistics

Threads
1,078,229
Messages
5,338,969
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top