Dynamic list drop down not updating

**garyi** · 05-23-2014, 07:37 AM

Hello I am using excel 2013

I want a data validation drop down list, the list may expand or contract.

I created the list in cells on one sheet and control T to make them into a table

In data validation I selected the range of cells in that table

When I add to the list it does not show in the drop down list.

Where did I go wrong?

cheers

**Pete_UK** · 05-23-2014, 07:40 AM

You should use a named range in the data validation to refer to those cells, rather than select them directly. The named range should be dynamic, so that it adjusts to the number of entries that you have.

Hope this helps.

Pete

**garyi** · 05-23-2014, 08:13 AM

As I understand it by making the selected range a table it became dynamic?

I tried both ways without sucess, I expect its me being a moron!

**garyi** · 05-23-2014, 09:04 AM

OK I fixed it, I was hoping to get away with not using OFFSET but I needed it.

However I was rather hopeful that if I changed the contents of one of the dynamic list cells it would change that name in the chosen data validation cells, is there anyway to achieve that?

**Glenn Kennedy** · 05-23-2014, 09:06 AM

I do not know what you are doing wrong. I have attached a sample sheet to show you. If you add a new value in A10, it is automatically included in the dropdown options. Can you post a sample sheet?

**Glenn Kennedy** · 05-23-2014, 09:08 AM

Similarly, if you change an entry in column A, the DD list in E1 changes, too...

**newdoverman** · 05-23-2014, 10:35 AM

You don't need OFFSET. Look at the table example given by Glenn Kennedy then click on the cell that has the Data Validation and go to Data, Data Validation and see how that table is being used to produce the dynamic list. When you add to the table the dynamic list expands to include the new entries. If you delete rows from the table, the dynamic list shrinks.