What do we mean by dynamic data validation? Suppose we are asking users to enter a product (or other item) into a cell, but we want them to select from a list that updates itself when new products are added to the source data list. So the source data list is linked to the dropdown that the user sees when they are on the cell that is asking for a product (or other item).
In Excel, we have a sheet that holds a list of our products.
How Do We Set This Up?
First of all we need a list. I have a small list of fruits. It starts out as just a column of fruit names with a header of Fruits.
- Format the source range to be an Excel Table. I would recommend naming the table tblFruits in our case.
- Name the range.
- Tell the Data Validation rules to pull the named range as your source.
To create a table you could use the shortcut Ctrl+T. To create a named range (step 2), go to the Formulas tab in the ribbon, and select the Name Manager. The Name Manager window will appear. Click on the New button. This brings up a new window that allows you to name your range. You could prefix the ranges with “rng” to make them easier to find in formulas. However, the naming style is up to you.
The data validation for the drop down is a List that points to a named range that points to a table.
The website Excel Campus has an article and video on this topic. The article is called How to Add New Rows to Drop-down Lists Automatically – Dynamic Data Validation Lists.