How to make a dynamic drop-down box

In the blog (Excel – How To Make A Drop-Down Dependent On Another Drop-Down), I showed how to make the contents of a drop-down box dependent on the contents of another cell or drop-down box. To do this, I used named ranges and the INDIRECT method.

If we use tables, the named range becomes dynamic – thus, if you add additional items to the bottom of the list, the named range automatically expands to include them.

To set up the table, select the data (including the title row) and press <CTRL><L>. This displays the table definition dialogue

Define table

Check that the data selected is correct and tick the “my table has headers” box.

To name the range, copy the text that you used for the table header (which needs to have no spaces) and paste it into the table name box under the Table Design menu

name a table

Now your named range is dynamic and will increase when you add more items to the list.