Update 12/12/2021
Since writing this post in 2017 Validation Dropdown lists based on Table Columns ranges, Excel now has new revolutionary dynamic array formulas that really has overtaken all the workarounds for better dropdown lists we used to do such as described in my post below. Now the new formula SORT() + UNIQUE() in Excel Office 365 (although not in earlier stand alone Excel versions) is simply the best way of returning a sorted dropdown list.
A quick demo on how…say your list data is in A2:A10 then
- in say C1 create your list enter = SORT(UNIQUE(A2:A10)) which gives you a dynamic array list of no blanks in between items and sorted.
- where you want your dropdown say H1 in the data validation dialog source field enter =C1#
That’s it. So much easier to implement than it used to be.
But better to place the dropdown list within a Table which is dynamic. Briefly how – convert the A2:A10 list to a Table (from a cell within this range Control T, My Table has headers unticked) change the column name to ‘MyList’ and change the name of the Table in the ribbon context Menu ‘Table Design’ to MyTable. Then change the range reference in C1 from A2:A10 to MyTable[Mylist] . Any new items to your table column list will be included because the dropdown in H1’s validation source field is pointing to a dynamic array list in C1 which in turn has a formula pointing to your new inherently dynamic ‘MyTable’ Table column, ‘MyList’.
For a dependent dropdown, you can use SORT() + UNIQUE() + FILTER() – see this nice short and sweet 3 min video from Bill Jelen Mr Excel https://www.youtube.com/watch?v=3eLzIMGpi5g on how.
I am leaving the post below to help people with older Excel versions.
Published Oct 17 2017
Up until quite recent versions of Excel (I am guessing a little here because I only have Office 365 so always have the latest and forget when I discovered this), if you wanted a dynamic list for your Validation Dropdowns, then the easiest way was to have the list in a Table and then create a range name say MyList1 for the column of the table you wanted to refer to and then refer to this range name in your validation source e.g. =MyList1. Great stuff, before Tables came along this source would have had to be quite a complex dynamic formula. So if you have a complex workbook you would probably be setting up lots of these Range Names for lots of Table columns you need to refer to in validation dropdowns and be quite happy about it because the lists refer to in these validation dropdowns would be be dynamic.
But in at least Excel 2016 onwards it’s even easier. You don’t need the Range Name anymore. There are two methods now you could use – one that almost doesn’t make sense and the other that does.
- The method that makes total sense is you add quotations ” ” around the Table structured reference as follows assuming the Table is named tblTest and the Table column I want to appear in the dropdown has a heading MyList2. By the way structured references is what we call Table reference syntax which can (and should) be used in formulas (such as MATCH, INDEX, VLOOKUP, SUMIFS) when you need to refer to the Table range or Table column range (or vector). Also by the way when I name Tables, I prefix the name with “tbl” because when I am writing a formula I only have to type “tbl” and all my named Tables will appear together in a list for me to choose then add “[” after the name to then see the column headers for that table which I can easily choose in the formula and finalise closing with the right “]” structured formula syntax.
=INDIRECT(“tblTest[MyList2]”)
This makes total sense. This is also new – Tables came out in Excel 2007 and you couldn’t do this – you had to create the Range Name then use this in the Data Validation so it was a two step process. In the latter images that follow I have a snapshot of the Data Validation dialog showing this. I will tell you later in the post which I prefer.
2) But an even easier way to set up that almost doesn’t make sense to me is to directly define the source within the Data Validation dialog – click in Allow to show List and then in the source field move your cursor to the Table column heading cell and then carefully moving your cursor to the top of this cell until the black downward arrow appears – you then left click and the cells immediately below the Table header should be highlighted excluding the Table column heading. [A quick aside – this is the same technique you can use to create dynamic Range Names from Table column headers which is what I used to do to use in the Data Validation source, but now you don’t need this range name if it’s only purpose was to feed the Validation Dropdown Source.]
At the same time in the Data Validation dialog source field an absolute reference to these cells appears. 
Well that’s no good if this is an absolute reference, I can hear you thinking, we wanted this to be dynamic? But this is the bit that looks like it doesn’t make sense to me.
If you add a new line to the Table and then check the Data Validation dialog Source seemingly absolute reference expands to include your new line. So it doesn’t appear like it’s going to be dynamic, but thankfully it is.
Note method #1’s Structured Reference definition in the snapshot of the Data Validation dialog.
So which method do I prefer? If you have a complex workbook application with lots of dropdowns linked to Table columns you will soon get lost with Method #2’s definition. Call me ‘old school’ but I prefer a definition and the Structured Reference tells you the Table and Column heading name. So I prefer Method 1, however if I were doing a quick and dirty spreadsheet, I would use Method 2. Also be careful with Method 1 – if you change the Table name or Table column heading – the dropdown won’t work until you manually fix the Structured Reference used which can be a problem when you are developing a complex application and it takes a while to get all your headings right. Method 2 will keep working so it’s really a good method if you only have a few Validation Dropdowns to do in a quick spreadsheet.