Validation Dropdown lists based on Table Columns ranges

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
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.

  1. 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.

Data Valiadation Dialog Finding the black arrow2) 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.  Data Validation defining the Table Col refWell 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.Data Validation Dialog After adding new table row

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.

Tagged with: , , ,