Table-based dropdown list does not dynamically update, as described by MS documentation

Question

Microsoft's documentation says that if I use a table for a dropdown list, then when revising that list, it will dynamically update:

"If you set up your list source as anExcel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you."

From Add or remove items from a drop-down list - Office Support (microsoft.com)

I've done this, but the table is not dynamically updating the range when I add items to it.

What must be done so that the feature described by Microsoft's documentation actually works as described?

Answer 1

Here's the solution:

https://www.excelcampus.com/tips/dynamic-data-validation/

or

https://www.contextures.com/xldataval01.html

Microsoft's own instructions are wrong.

Answer 2

Hi hans.pnw,

Thanks for sharing updates and share a solution link here , it will also help for other members in this community. If you need any help, please don’t hesitate to post in the forum. please feel free to post back in this community.

We will be here for help.

Best Regards,

Waqas Muhammad

Answer 3

Hi hans.pnw,

As per your description, I just do an Excel according to official article when I add new items at the end of create dropdown list it will updates the range automatically.

As you mentioned, "There is absolutely no difference between creating a dropdown list without a table " I can see difference without table see my below test image when I add new item in dropdown list it will not added new item in dropdown list without table.

If it is convenient for you, could you please send the one of sample Excel file or your recording video to me so that I can take a look from my side and help you to check the scenario.?

If there is confidential information, then you can send it to me in Private Message

Meanwhile, What’s your Excel version? Open Excel > File > Account > Production Information and share a screenshot of it.  (Please mask your private information.)

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 4

I set up a test doc and tried again, but the problem persists.

I've determined that Microsoft hasn't actually created a dynamically updating dropdown list table, after all, despite their documentation claiming otherwise.

Here's what's going on: when a blank cell is activated at the bottom of the dropdown list table, one cell below it -- to be clear, outside of but adjacent to the table -- and an item is added, as you know, Excel automatically extends the range of the table down and includes that newly added cell in the table. With
VBA, I watched the table range expand every time I added another item to the bottom of the table.

Based on Microsoft's documentation describing that "all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you,"

a reasonable person would conclude that because the table range is automatically extending down to include newly added items at the bottom edge of the table, the range for the dropdown list must surely also be simultaneously extended.

It is not.

There is absolutely no difference between creating a dropdown list without a table -- you still have to define a range, and you still CANNOT add any items to the dropdown list unless they are between the two extremes of the range.

Put another way, in fact, you CANNOT just "add or remove items from the list," and have "Excel ... automatically update any associated drop-downs for you." You can only do what has had to be done for years: be sure that any additions or deletions are made within the range you defined when you created the dropdown list and associated it with data validation.

It stands to reason that if a user can watch a table range dynamically expand, and if watching that range expand via VBA confirms it, then that range ought to expand in the Data Validation component to which the range points.

And it stands to reason that a logical place for a user to add to a list of items is at the bottom of the list, especially if they are already accustomed to do ing so in a table and having that table expand to include the newly added item.

Answer 5

Hi,

This works quite well.  Share the link from where i can download your problematic file.