This Excel tutorial explains how to create dynamic Data Validation list that automatically adjusts the data Range.
You may also want to read:
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.
In Sheet1, select the Cells you want to add Data Validation > click on DATA tab > Data Validation
Select “List” and select the data range of fruit list for the Source > OK
Go back to Sheet1, now you can select the fruit list in A2:A6
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
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
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
You can see that the fruit_list has expanded the Range.
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
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.
