Data Validation is an important feature in Excel. In this article, we will see how Excel data validation based on another cell is created. Data validation makes a list more creative and user-friendly. Instead of having data in different cells of a column, you have the option to choose any data based on a list in a cell. Here in this article, we will see the process of creating a dependent list using Excel Data validation. We will also see the process of restricting data entry in a range of cells with data validation.
Data Validation using the INDIRECT Function based on Another Cell Value
In this example, we will create a main drop-down list along with 2 dependent lists. In sheet S1, create 3 lists of Item, Ice Cream, and Juice.
For creating the list using the data validation. Follow the process below.
- Select each heading of the list in sheet S1 and format them as tables by choosing Format as Tables under the Home Choose any Format Style you want while formatting the lists as tables.
- After completing the formatting of each lists your worksheet will have a filter option on each list.
- Now for each list, we have to create an individual Name Range in the Name Box. Select cells A3: A4 and in the Name Box write Item. In the same way for the range C3: C7 write Ice_Cream and for range E3: E7 write Juice.
Creating Main Dropdown List
- Create another worksheet named S2. In S2, make two headings as Item List and Flavor in cell B2 and C2.
- After this Format the headings as Table using the Format as Table option.
- Now select cell B3 of sheet S2 and click on the Data Validation under the Data tab.
- In the Data Validation dialogue box select List as validation criteria and write =Item in the source option.
- Now after pressing Ok in the Data Validation dialogue box, you will get to see a dropdown list in cell B3.
Creating Dependent Dropdown List
- Select cell C3 and press on the Data Validation under the Data tab option.
- In the Data Validation dialogue box select List and write =INDIRECT(B3) in the source option.
- After Pressing OK, you will see the below box, Press Yes there to continue.
- Now after selecting the item in cell B3, you will see other options that will appear in cell C3 as well. The INDIRECT function used here will be categorized the items. Like, after selecting Ice_Cream in cell B3 you will find the exact Ice Cream flavors in cell C3 which exist in the list of S1.
- Again, while having the name Juice in cell B3 you will get a range of options in cell C3.
Creating 3 Lists based on Data Validation
Here we will create two worksheets. In the first worksheet, we will insert the information about the task that we are going to perform. We will create a 3-dropdown list of dress, color, and size. The procedure is given below.
- In S3 create three lists of Dress, Color, and Size.
- After creating the list, we will format each list as a table. For this, select any cell from the list, and under the Home tab select the Format as Table option.
- After clicking the Format as Table option, you will get a number of the table style. After selecting a table style, you will see the Format as Table dialogue box. In this box select the range as $A$1:$C$8 and press
- After formatting the table, we will create a named range for each of the lists. Select A2: A7 and write Dress in the Name Box. For range B2: B8 write Color and for range C2: C6 write Size.
- After doing this, create a new worksheet named S4. In S4, create 3 headings as Name, Color, and Size in A2, B2, and C2.
- After making the headings, select the Format as Table under the Home tab and after selecting the table style, in the Format as Table dialogue box select the range and put a tick on the My table has header option.
Making of the Dropdown List
- Now select the cell B3 and under the Data tab select Data Validation.
- In the Data Validation dialogue box select validation criteria as List and write =Dress in the Source section.
- After pressing OK. You will see a dropdown list that will appear in cell B3.
- Now follow the same procedure in cell B3. In the Data Validation dialogue box, write =Color instead of Dress.
- Again, select cell C3 and perform the data validation. In the Source section of the Data Validation, write =Size and press OK.
Restrict Value Entry with Excel Data Validation Based on Another Cell
We can restrict data entry for a range of cells using Excel Data Validation. To perform this, select a range of cells and under the Data tab select the Data Validation option.
In the Data Validation Dialogue box, select the Custom option as Validation Criteria. In the formula section, write
<code>=$B$2=”Insert Data” and press OK.
Now in the selected range put any data you want and press enter. You will see the below result.
Now, in cell B2 write Insert Data and put any data in the selected range. You will see the data gets inserted. This is because we made a custom validation using a formula. Whenever the Insert Data is available in cell B2, only then you can insert values in the selected range.
Download The Working File
In this article, we saw how to make lists using Excel Data validation. We created a dependent list by Excel Data Validation Based on Another Cell where we used the INDIRECT function. We saw how data entry can be restricted using the data validation based on another cell. This article might be useful for many statistical operations. Hope you will like this article. Stay fine and comment below if you face any difficulties regarding this article.