Excel Data Validation allows you to limit what value(s) may be entered in a cell or range. You can limit entries to positive integers, text, dates, and much more. In this tutorial, we are going to look at how to create a Data Validation Drop-Down List in a cell using VBA. As an example, A user can specify a meeting scheduled between 9:00 AM and 6:00 PM. As we can use data validation in excel to make sure a value is a positive number, a date between 15 and 30, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters etc.
Data Validation in Excel is a feature that allows you to control or restrict the type of data that you can enter in to a cell in a workbook. When used properly, data validation can help you prevent users from entering invalid values in an excel workbook. This reduces potential errors and can save you a lot of time.
You can download an excel workbook with various examples of data validation in excel using simple, named lists and dynamic named lists.
A simple data validation example in Excel
Select a cell where you want to restrict the entry of data and then simply go to the menu bar and choose ‘Data’ and then ‘Validation’. You can then choose the type of criteria to check against when the user enters a value in the cell. Using the options provided, you can restrict the entry to whole numbers, dates, strings and my personal favorite – a custom list.
In our example, we will take the case of a custom list. We want to restrict the entry of data in cell C9 to only a list of names. Well, choose ‘Data’ and then ‘Validation’ from the menu and enter a list of names in the Data Validation drop-down as shown in figure.
You can then select the ‘Error Alert’ tab and enable the ‘Show error alert after invalid data is entered’ check box.
Now whenever the user tries the enter anything apart from the list of names (that you entered in the data validation box above), the system will show an error message to the user and prevent wrong data entry in that particular cell.
Data Validation in Excel using Named (drop-down) Lists
If you are not too familiar with what a named list is, I suggest that you learn about them in the article dealing with creating chart using Named Range in Excel. Once we know how to define static and dynamic named lists, we can use them to restrict entry of data in the cells we want. In our example, we have named lists defined for name, country and food. In our example, we want to show only the list of names to the user. But that’s not all, we also want to restrict the number of names he/she can see. So we create a dynamic named list using the OFFSET function which refers to another cell (which in this case is cell M2) for knowing how much of the data to show. In our example, we want to show the user only two names, so we simply enter 2 in cell M2. The OFFSET function then takes the first cell in the list of names (cell B3) and creates a range of cells between that cell and the one is offset from B3 by two rows (cell B4). The resulting range thus is cell B3:B4.
How to set-up data validation in Excel using Named Lists
1. Lay out the data as shown in figure above.
2. Create a new named range called ‘dynamicnames1’ using ‘Insert’ -> ‘Name’ -> ‘Define’ from the menu bar.
3. Enter the formula ‘=OFFSET(validation!$B$3,0,0,validation!$M$2,1)’ as the criteria for the range in the form that appears.
4. Now select any cell and cell and click on the menu options – ‘Data’ and then ‘Validation’.
5. In the ‘Source’ enter the name of the above list as ‘=dynamicnames1’.
2. Create a new named range called ‘dynamicnames1’ using ‘Insert’ -> ‘Name’ -> ‘Define’ from the menu bar.
3. Enter the formula ‘=OFFSET(validation!$B$3,0,0,validation!$M$2,1)’ as the criteria for the range in the form that appears.
4. Now select any cell and cell and click on the menu options – ‘Data’ and then ‘Validation’.
5. In the ‘Source’ enter the name of the above list as ‘=dynamicnames1’.
A quick tip – Ensure that whenever you create a named range, reference to any cell should preferably be absolute, ie. $M$2 rather than M2. This can prevent a lot of headache later if you add new columns and cells to the worksheet.
You can now go back to the excel worksheet and check the cell where you restricted the data entry using data validation. In the drop-down, you will find only two names appearing which means that the data validation is working.
In one of the other examples, using data validation and a named range, we not only decide how many values to show in the drop-down but also which of the pre-defined lists to show as drop-down. You can also use conditional formatting in excel along with data validation to achieve great results.