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.
What Is Data Validation in Excel?
Data validation is an Excel feature through which you can create rules that what kinds of data you would like to enter into the cell. So, basically, it allows you to apply any rules while entering any data. There are so many different validation rules. For example, you can only allow numeric or text values in a cell through data validation or can allow numeric values within a specific range. Data validation can restrict dates and times outside of the given range. It helps us to check the accuracy and quality of data before using it. Data validation provides several checks to ensure the consistency of the input or stored data.
How to Do Data Validation in Excel
To do data validation in Excel, you need to define the data validation rules. After that, if you enter any data, data validation will work on it. If the data meets the data validation rules, it will put the data on the cell. Otherwise, it will not show an error message.
First, take a dataset that includes student ID, student name, and age. We would like to make a data validation where the age must be less than 18.
Then, select cell D11. After that, go to the Data tab on the ribbon. Then, select the Data validation drop-down option from the Data Tools group.
As a result, the Data Validation dialog box will appear. From there select the Settings tab. Then, select the Whole Number from the Allow section. After that, check on the Ignore Blank option. Next, select the Less Than option from Date. Then, Set the Maximum value as 18. Finally, click on OK.
Next, if we write 20 as age, it will show an error because it is above our maximum limit in the data validation. That’s what we get from data validation.
4 Suitable Examples to Do Data Validation Based on Another Cell in Excel
To use data validation based on another cell in Excel, we have found 4 different examples through which you can have a clear idea. In this article, we will utilize the INDIRECT function and named range to use data validation. We will also use cell reference and how to restrict value entry to data validation. All of these methods are fairly easy to use. To understand these clearly, follow the methods properly.
1. Applying INDIRECT Function
Our first method is based on using the INDIRECT function. In this method, we would like to use this INDIRECT function in the data validation dialog box. This function helps us to change the drop-down option according to a certain cell. We take a dataset that includes two items and their different types.
To understand the method clearly, follow the steps
Steps
- First, convert all three columns into different tables.
- Then, select the range of cells B5 to B6.
- As a result, the Table Design tab will appear.
- Go to the Table Design tab on the ribbon.
- Then, change the Table Name from the Properties group.
- Then, select the range of cells D5 to D9.
- Change the Table Name from the Properties group.
- Finally, select the range of cells F5 to F9.
- Then, change the Table Name from the Properties group just like the previous way.
- After that, go to the Formula tab on the ribbon.
- Select Define Name from the Define Names group.
- Then, the New Name dialog box will appear.
- Set the name.
- In the Refers to section, write down the following.
=Items[Item]
- Click on OK.
- Then, create two new columns where we would like to add data validation.
- After that, select cell H5.
- Then, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select List from the Allow
- After that, check on the Ignore blank and in-cell dropdown options.
- Then, write down the following in the Source section.
=Item- Finally, click on OK.
- As a result, you will get the following drop-down option where you can select either ice cream or juice.
- select cell I5.
- Then, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select List from the Allow section.
- After that, check on the Ignore blank and in-cell dropdown options.
- Then, write down the following in the Source section.
=INDIRECT(H5)- Finally, click on OK.
- As a result, you will get the following drop-down option where you can select any flavor. Here, we get the following flavor for ice cream.
- Now, if we choose juice from the item list, the flavor will change accordingly.
2. Use of Named Range
Our second method is based on using the named range. In this method, you can apply a name to the range in the Table. Then, use this table name in the data validation dialog box. We take a dataset that includes dress, color, and size.
To understand the method, follow the steps.
Steps
- First, create a table using the dataset.
- To do this select the range of cells B4 to D9.
- Then, go to the Insert tab on the ribbon.
- Select Table from Tables group.
- As a result, we will get the following result, See the screenshot.
- Next, go to the Formula tab on the ribbon.
- Select Define Name from the Define Names group.
- Then, the New Name dialog box will appear.
- Set the name.
- In the Refers to section, write down the following.
=Table1[Dress]- Then, click on OK.
- Then, again select Define Name from the Define Names group.
- Then, the New Name dialog box will appear.
- Set the name.
- In the Refers to section, write down the following.
=Table1[Color]- Then, click on OK.
- Do the same procedure for size also.
- Now, create three new columns.
- Then, select F5.
- After that, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select List from the Allow
- After that, check on the Ignore blank and in-cell dropdown options.
- Then, write down the following in the Source section.
=Dress- Finally, click on OK.
- As a consequence, we will get the following drop-down options for the dress.
- Then, select G5.
- After that, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select List from the Allow section.
- After that, check on the Ignore blank and in-cell dropdown options.
- Then, write down the following in the Source section.
=Color- Finally, click on OK.
- As a consequence, we will get the following drop-down options for the color
- Then, select H5.
- After that, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select List from the Allow section.
- After that, check on the Ignore blank and in-cell dropdown options.
- Then, write down the following in the Source section.
=Size- Finally, click on OK.
- As a consequence, we will get the following drop-down options for the size.
3. Applying Cell References in Data Validation
Our third method is based on using the direct cell reference in data validation. In this method, we would like to use the cell reference in the data validation dialog box. As a result, it will provide us with a drop-down option. Here, we take a dataset that includes states and their sales amount.
To understand the method, follow the steps.
Steps
- First, create two new cells including states and sales amount.
- Then, select cell F4.
- After that, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select List from the Allow section.
- After that, check on the Ignore blank and in-cell dropdown options.
- Then, select the range of cells B5 to B12.
- Finally, click on OK.
- As a consequence, you will get a drop-down option where you can select any state.
- We would like to get the sales amount of the corresponding state.
- To do this, select cell F5.
- Then, write down the following formula using the VLOOKUP function.
=VLOOKUP(F4,$B$5:$C$12,2,0)
- Click on Enter to apply the formula.
- Then, if you change the state from the drop-down option, the sales amount will change automatically. See the screenshot.
4. Restrict Value Entry with Data Validation
Our final method is based on how to restrict value entry with data validation. In this method, we would like to use data validation and apply some rules through which the data entry becomes limited. If you enter any data within the given range, it will allow us to put it in the cell, Otherwise, it will show an error. We take a dataset that includes order ID, item, order date, and quantity.
Steps
- In this method, we would like to restrict the order date from 1 January 2021 to 5 May 2022. Outside of this range will show an error.
- To do this, select cell D10.
- go to the Data tab on the ribbon.
- After that, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select Date from the Allow section.
- After that, check on the Ignore blank option.
- Select the between option from the Date section.
- Then, set the start and end dates.
- Finally, click on OK.
- Now, if we put a date on cell D10 which is outside of the range, it will show us an error. See the screenshot.
How to Do Data Validation Based on Adjacent Cell in Excel
We can do data validation based on an adjacent cell. For example, you define a certain text in the adjacent cell, Now, if you put that in data validation and define that there is no way to write on the next column until the condition meets. You can easily do it in the adjacent cell. We take a dataset that includes several exams, opinions, and reasons. We would like to write something in the reasons column if the exam opinion is hard.
To understand the process, follow the steps.
Steps
- First, select the range of cells D5 to D9.
- After that, go to the Data tab on the ribbon.
- Then, select the Data validation drop-down option from the Data Tools group.
- As a result, the Data Validation dialog box will appear.
- First, select the Settings tab on the top.
- Then, select Custom from the Allow section.
- After that, write down the following formula in the Formula section.
=$C5="Hard"- Finally, click on OK.
- Then, you can add descriptions in the reasons columns when the adjacent cell value is Hard.
- But, if we try to add a description when the adjacent cell value is different, then it will show us an error.
Conclusion
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. Don’t forget to visit our Exceldemy page.
Related Articles
- Excel Data Validation Alphanumeric Only (Using Custom Formula)