Excel: Use Table data as drop-down list

I am sure you have tried this:

This is just s sample of listing Geographic areas from a table. The underlying data can come from any data source but we are summarising it in Excel as a table.

1. Select your target cell

2. Select Data Validation > List

3. Select your source as a column in a table

5. Everything works fine..

6. ..until you add more lines of data and then your validation fails.

Well, to be hones, it does not fail. It just still references the range you specified.

But if you try to do a direct table column reference as the source in the data validation Excel will fail

Now how to work around this. Use a Name.

1. Select your data column. In this case the GeographicRegion table is used.

Do this by holding the mouse on the top of the column name until you get the down arrow, the range that gets selected is shown below this selection for clarification.

2. Define the name by going to: Formulas > Define Name.

And fill in a name for your range.

3. Go to your original cell and select data validation again.

Change the source to your new name.

4. Now you have data validation from a table column.

5. You can now add/remove data from the table and it will reflect instantly in your drop-down list

Adrian