CREATE DYNAMIC DATA VALIDATION LIST

Say your data is in the range A2:A6 and you want to create a data validation list using this data  in C1. It is easy to do. You will go to Data-- Data Validation--List--Select Range--Press OK and you are done.

But sometimes we want our data validation list to get updated automatically when we add entries in our data.
For this we need to create a dynamic named range for our data. To do this go to Formula Tab--Click on Define Name. Below window will appear.

Enter name whatever name you want in the NAME field (I have named it as MyRange) and enter the formula as shown in the figure in refers to field and press OK

Formula :  =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Select cell C1(where you want to create data validation list) and Go to Data Tab--Click on Data Validation. Below window will appear

Select List in the combobox under Allow option and enter the name (=MyRange) that you have given in the previous step and press OK.

Now whenever you add an entry in you data, your list will automatically get updated. I have add few names and my list got updated. See image below

Is this post helpful to you?

Please post your valuable comment. Thanks!