Validation Dropdown lists based on Table Columns ranges
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.
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.