In last week’s blog post and videoI covered the subject of Dynamic Arrays and how and when to use the hash sign in a formula.

This week, I’m taking it a step further, tackling a real-world application of these concepts: creating an auto-updating drop-down list that responds in real-time to changes in the source data.

Take a look at the screenshot above. You’ll see that cell G1 contains a drop-down list. The magic happens with the Dynamic Array that starts in cell K1. Each time you add a new row to the table in columns A to D, Excel does something clever.

If the ‘Flavour’ in the new row is a fresh entry in column A, i.e. it doesn’t exist in any of the previous rows, Excel automatically appends it to the list in column K. Not only that it puts it in the correct place, alphabetically.

This means the drop-down in G1 refreshes on its own, without needing any additional input from you. Isn’t that efficient?

If you want to know how it all hangs together, check out the video below…