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


image

2. Select Data Validation > List


image

3. Select your source as a column in a table


image

5. Everything works fine..


image

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


image
image

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


image

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.


image

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

And fill in a name for your range.


image

image
image

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

Change the source to your new name.


image

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


image

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

Adrian

0 views0 comments

Recent Posts

See All