This post from ExcelFORO explains how to use VBA to create a sorted data validation list. It’s a great solution! Are there other ways to solve this?
(download my Excel file)
Sorted Drop Down List
The VBA code instantly refreshes when new entries are added. Perfect!
What about a formula solution?
Scroll to the comments section of his post. There’s a suggestion to use a dynamic array! These are being tested (365 insider edition) so I created a formula solution that works in all versions.
My solution contains:
- city name input in field ‘ciudades’
- four helper formula fields (steps 1 to 4)
How does it work?
- Rank =COUNTIFS([ciudades],”<=”&[@ciudades]) ‘A Coruna’ = 1, ‘Zaragoza’ = 6(last)
- Counter =ROW([@ciudades])-ROW(Table13[[#Headers],[ciudades]]) basic counter
- Match =MATCH([@Counter],[Rank],0) find Counter value in Rank
- Sorted =INDEX([ciudades],[@Match]) index to get sorted city via Match
- Data Validation connects to a named range that references step 4 Sorted
Add a city below ‘Valencia’. The Data Validation list connects to a named range and updates instantly.
The helper columns are light and easy to audit. When you add a city the table, including all formulas, automatically expands!
Both the vba and formula solutions are worth considering.
UPDATE: Alternative Formula Solution
In the comments section below Robert Gascon suggested a solution that does not depend on an Excel table (my solution does).
I have now updated my Excel file (found at top of post) to include Robert’s solution! I’ve learned so much from Robert over the past few months!
What about a Power Query solution?
Power Query is an amazing tool that could load the ‘ciudad’ text, sort it and return it back to the sheet.
There’s one issue…if you add/modify city entries you need to refresh the query.
Using ‘Worksheet_Change’ the vba code updates automatically based on any addition or modification. The formulas are also automatic.
Is Power Query a bad solution? Not necessarily. If you had a large amount of data and didn’t want (a) heavy formulas or (b) vba code then Power Query could be the perfect solution! Power Query would load the data, do all the necessary steps, and then quietly drop the answers back into the sheet.
UPDATE: ExcelCampus has a nice blog post showing 4 different solutions. Jon’s formula solution is the way to solve it for those with the new 365 version of Excel. The solutions in my post by myself and Robert are compatible in any modern version of Excel (back to Excel 2007).
excelforo.blogspot.com is one of the best Spanish Excel blogs that you’ll find! I can read Spanish fairly well so when I read Ismael’s posts my Excel and Spanish hobbies collide! Coool! 🙂
Check out Ismael’s Facebook page!
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
I saw a friend working on an Excel model back in 1996. I was hooked for life!
It took awhile to get into the workforce…but it’s been an amazing ride!