Excel dynamic Data Validation list

This Excel tutorial explains how to create dynamic Data Validation list that automatically adjusts the data Range.

You may also want to read:

Excel Dynamic Print Area

Excel dynamic data range

Excel graph dynamic data range

Create Excel Data Validation list

Excel Data Validation list restricts users to select values from a drop down box. When the input is different from the predefined list, an error message will pop up.

Before we create a dynamic Data Validation list, let’s briefly recap how to create a static Data Validation list.

Assume that we want to create a Data Validation list for fruit, input the fruit list in the worksheet data.

Excel dynamic Data Validation list 01

 

In Sheet1, select the Cells you want to add Data Validation > click on DATA tab > Data Validation

Excel dynamic Data Validation list 02

 

Select “List” and select the data range of fruit list for the Source > OK

Excel dynamic Data Validation list 03

 

Go back to Sheet1, now you can select the fruit list in A2:A6

Excel dynamic Data Validation list 04

However, we defined the fruit list as data!A2:A5, if we want to add more fruits, we will have to change the Source again. Ideally we don’t have to change the Source every time we add or remove fruit.

Create dynamic Name Range for dynamic Data Validation list

To dynamically create a data range,  create a Name Range.

Click on FORMULAS tab > Name Manager > New

Excel dynamic Data Validation list 06

Name the Range as fruit_list, under Refers to, type

=OFFSET(data!$A$2,0,0,SUMPRODUCT(MAX((data!$A:$A<>"")*ROW(data!$A:$A)))-1,1)

If you don’t understand the OFFSET function, click here to view my previous post.

But simply speaking, the syntax of OFFSET function is as below

OFFSET( reference, rows, columns, [height], [width])

 

Now add one more fruit at the bottom to test if the fruit_list has been updated to include new fruit

Excel dynamic Data Validation list 07

 

Go to the Name Range and click on the Range button on the right of Refers to, this will highlight the Range the formula refers to

Excel dynamic Data Validation list 06

 

You can see that the fruit_list has expanded the Range.

Excel dynamic Data Validation list 08

Update List Source as dynamic Data Validation list

In Sheet1, select the Cells you want to update Data Validation > click on DATA tab > Data Validation

Excel dynamic Data Validation list 09

In the Source, update the value as =fruit_list  (make sure to include the = sign)

 

Now you can see “new fruit” is included in fruit list. Whenever you add a fruit in worksheet data, you will see the change being reflected immediately in the Data Validation list, this is why I call it dynamic Data Validation list.

Excel dynamic Data Validation list 10

Outbound References

https://support.microsoft.com/en-us/kb/324991

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *