This video tutorial explains how to create source data for a drop down list that is in a dynamic range. A dynamic range automatically expands to include new items that you add to the end of a list.
There are a number of ways of achieving this, but the video looks at using Excel table functionality.
The first step involves converting each of the source data lists into an Excel table. The video clearly shows you how to do this from 01:40. You click into your list and then on the Ribbon INSERT tab click the Table button. The video then shows you how to name the tables from 02:18. The naming step is quite important as it helps you later on when you need to refer to the table in data validation.
Start at 03:45 you will see that using data validation you can’t refer directly to a table name as your list source data. The workaround to this is create a named range that refers to the table. The video explains how to use the Name Manager to create the names starting at 05:10.
From 06:45 the video show you how to specify the source data for your drop down lists using the names that you have created.