16 Apr 2019

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.

Pic 1 Kevin’s Formula Solution

 

 

 

 

 

 

My solution contains:

  • city name input in field ‘ciudades’
  • four helper formula fields (steps 1 to 4)

 

How does it work?

  1. Rank =COUNTIFS([ciudades],”<=”&[@ciudades])    ‘A Coruna’ = 1, ‘Zaragoza’ = 6(last)
  2. Counter  =ROW([@ciudades])-ROW(Table13[[#Headers],[ciudades]]) basic counter
  3. Match =MATCH([@Counter],[Rank],0)   find Counter value in Rank
  4. Sorted =INDEX([ciudades],[@Match])   index to get sorted city via Match
  5. 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.

Pic 2 Data Validation

 

 

 

 

 

 

 

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.

 

What about a Power Query solution?

Sure. Power Query is an amazing tool that could easily 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.

 

 

About ExcelFORO

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!

 

 

 

About Me

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!

Post a comment