1.   So what we want to do is we want to create a data validation with dynamic range i.e. as the range is populated with new entries the data validation should automatically get updated with the new entry. So here I have the data validation in ‘C5’ and the list starts from ‘G6’.

2. First we define a named range by doing to Insert>Name>Define or using the short cut Alt + I +N +D. As shown in the picture for the time being let us define the range as a simple range from ‘G6:G10’ later we shall make it dynamic.I have the name as ‘Range’
3. Then put a validation, Go to Data>Validation. Then in the settings tab of the ‘Data Validation’ pop up that appears in the ‘Allow’ drop down select ‘List’ and in the source box put ‘=Range’ as shown in the picture. This will give you a data validation on the cell C5.

4. You can add a warning to the Data Validation by going to the ‘Error Alert’ tab and selecting a ‘Style’ and a can add an Error Message.

5. Now to make the range dynamic. Again go to Insert>Name>Define then select the earlier defined name ‘Range’ and change the Refers to part to “=OFFSET(Sheet!1$G$5,0,0,COUNTA(Sheet1!$G5$:$G$3000),1)”.

6. We are done. Check by adding a name to the G column like I have added ‘David’ here.

Blog Widget by LinkWithin

Search this blog..