Data Validation – Dynamic (Excel 2010) – From Table

Data Validation – Dynamic (Excel 2010) – From Table

Data validation is used so users enter the expected data only, available in a drop down. But with static data validation, if we want to add more values in our list, we will first have to update the source list and then update the source range in Data Validation.  

But there is a way to handle this issue. For example, in column A we have a few product’s listed which we want in another cell to appear in drop down. And as per our need when we add/modify another product in the list (Column A), it start appearing in the drop down.

1- For this, first create a table of the list, for this select the data A1:A9 and press Ctrl T.

2- The default table name is Table1, if you want to change then have your cursor somewhere in the table and go to design tab, under table name the name is appearing and can be changed.

3- Select all record of the table excluding the header (A2:A9) then go to Formulas tab > Define Name and give a name to highlighted range i.e. product

4- In the Refers to tab refer the table like in our case =Table1

5- Select Cell C1 and go to Data > Data Validation. In the Data Validation dialog box, settings tab, choose list under Allow: and in the source box write equal sign and the named range. Like in our example it would be =Product. Press Ok.

6- The drop down list is available in cell C1

7- Now important thing is that we can add a new value to our list, simple go to cell A10 and type another product and press ok or move out of cell and check in C1 cell, the new product is in the list.

Please refer to below snapshots for better idea.