Data Validation Based on Another Cell in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Do Data Validation in Excel

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.

Applying INDIRECT Function to do Data Validation Based on Another Cell in Excel

  • As a result, you will get the following drop-down option where you can select either ice cream or juice.

Implementing INDIRECT Function to do Data Validation Based on Another Cell in Excel

  • 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.

Using INDIRECT Function to do Data Validation Based on Another Cell in Excel

  • 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.

Utilizing INDIRECT Function to do Data Validation Based on Another Cell in Excel

  • Now, if we choose juice from the item list, the flavor will change accordingly.

Exercising INDIRECT Function to do Data Validation Based on Another Cell in Excel

Read More: How to Use IF Statement in Data Validation Formula in Excel


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 Formulas 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.

Use of Named Range to Do Data Validation Based on Another Cell in Excel

  • 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.

Utilizing Named Range to Do Data Validation Based on Another Cell in Excel

  • As a consequence, we will get the following drop-down options for the color

Exercising Named Range to Do Data Validation Based on Another Cell in Excel

  • 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.

Applying Named Range to Do Data Validation Based on Another Cell in Excel

  • As a consequence, we will get the following drop-down options for the size.

Creating Named Range to Do Data Validation Based on Another Cell in Excel


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.

Applying Cell References to Do Data Validation Based on Another Cell in Excel

  • As a consequence, you will get a drop-down option where you can select any state.

Using Cell References to Do Data Validation Based on Another Cell in Excel

  • 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.

Utilizing Cell References to Do Data Validation Based on Another Cell in Excel

Read More: How to Use Custom VLOOKUP Formula in Excel Data Validation


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.

Restrict Value Entry with Data Validation in Excel

  • Now, if we put a date on cell D10 which is outside of the range, it will show us an error. See the screenshot.

Read More: How to Remove Data Validation Restrictions in Excel


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.

How to Do Data Validation Based on Adjacent Cell in Excel

  • 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.

Read More: How to Apply Multiple Data Validation in One Cell in Excel


Download Practice Workbook

Download the practice workbook below.


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.


Related Articles


<< Go Back to Excel Custom Data Validation | Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

19 Comments
  1. Thank you for the article, it’s useful, short and well explained.

  2. Thanks this was helpful

  3. Thanks a lot for sharing. Well explained

  4. Why when I put in the formula =Items, excel gives me an error message and says that there is a problem with the formula? I formatted as tables and followed all the steps. Excel even recognizes the name and goes there, but gives the error message. Thanks

    • It’s kind of old but maybe it will be useful for someone else in the future.
      When You use data validation u have to enter Named range in list range.
      If you enter table name You will get message about bad formula.
      I personaly dont like Named ranges because it’s troublesome to add new values.
      Instead I use only Tables. In Data Validation I use =INDIRECT(“TableName”) instead =TableName
      It works like a charm and Table keep it’s size by itself.

  5. Looking for help.. I have a spread sheet with data validations and formulas that once I select a piece of material from the list the cell populates with the price referenced on another sheet.

    =IFERROR(VLOOKUP(B25,’Material Pricing’!$A$2:$C$313,3,0),””)

    I want to be able to change the year on sheet 1 i.e from 2023 to 2024 (from a separate cell) and once I select the material from a dropdown have the price value change to reference the corresponding values in the column under that year which is in the other sheet.

    Can this be done?

    • You can do this by combining IF function with your formula.
      Create multiple tables for your price information for the years. Refer the year to a cell and the use it to change the VLOOKUP’s “table_array”.

      For example the year is on cell F4,

      now you can type, =IF(F4=2023,IFERROR(VLOOKUP(B5,’Material Pricing’!B5:D7,3,0),””),IF(F4=2024,IFERROR(VLOOKUP(B5,’Material Pricing’!B13:D15,3,0),””),””))

      If the year is 2023, then the “table_array” will be B5:D7
      IF 2024, it will be B13:D15.
      Else it will return a blank.

      To know more about this, you can read this.

  6. thanks, I was looking for this!!!
    One question – do we need to have an Underscore “_” or dash “-” between two words in the Name range or Name cell?

    • Thank you for your question. You need to use Underscore (“_”) between two words in the Name Range.

  7. Hi Guys,

    I need some help in creating a data validation. I want to essentially be able to select the data from a list, which I am able to do, but then, basis that selection in the next cell I want items specifically from that list.

    Eg. I apply a formula in Cell Z2 so that it lets me select from that list 🙂
    in Cell AA2 now, I want to be able to reference that so that I only get the options that are relative to the value I selected in Z2. Is that possible?

    • Hey Ash, thank you for your question.

      Suppose, we have these three items in cell Z2 as a Dropdown List:
      X
      Y
      Z
      Then, if we choose X, then it should display as a list in AA2 cell:
      P
      Q
      R

      Now, to do that, select cell range that contains PQR (eg. T1:T3), then use Name Range
      PQR as X. Similarly, name the ranges as Y and Z for their corresponding cell ranges.

      Next, use Data Validation on cell AA2.
      Allow: List
      Source: =INDIRECT(Z2)

      Then, it should do what you want.

      TLDR; Use Name Range and INDIRECT function inside Data Validation.

  8. Can you please explain why you use separate sheets instead of just one sheet for your tables? I have always used on sheet, labelled “LISTS” for all of my data validation tables. The reason I’m here now is because I need to select data from one list based on data in another list. I’m not sure why this would need more than one sheet.
    Thanks.

    • Hi Mary, thanks for your response. You can definitely apply this process in a single sheet. However we use different sheets to show the different processes for the same output. And it also seemed convenient to use separate sheets to show the application of using the data validation list for different conditions. So different sheets for different methods were used in this article. Hope that provides you with the answer to your question.

  9. incredibly confusing. there is no need to format as table. Just create the filter in the header row. using named ranges prevents people from intuitively understanding what range you’re talking about – REFERENCE RANGES EXPLICITLY! tutorials should not use either named ranges or references to other sheets. that just makes it more difficult to understand. set up your example on one sheet and let US move lookup tables to other sheets as needed.

    • Hi Steve, we are extremely sorry for your trouble. Named ranges are pretty useful when we need to use a range repeatedly. A lot of our readers are okay with it. Going through the dataset can help anyone to understand the defined range of a named range. But we are taking your feedback sincerely. We’ll apply your suggestion in the upcoming articles.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo