Excel Data Validation Based on Another Cell

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

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.

Excel Data Validation Based on Another Cell

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 filter option on each list.

  • Now for each list, we have to create 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.

Excel Data Validation Based on Another Cell

  • Now select cell B3 of sheet S2 and click on the Data Validation under the Data tab.

Excel Data Validation Based on Another Cell

  • In the Data Validation dialogue box select List as validation criteria and write =Item in the source option.

Excel Data Validation Based on Another Cell

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

Excel Data Validation Based on Another Cell

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

Excel Data Validation Based on Another Cell

Note: Here in the Item list of Sheet S1 we used the name Ice_Cream instead of Ice Cream. The reason for using an underscore symbol is that while indicating a name in the Name Box you cannot use “-” or space in between characters. Our Name Box contains name from the list Item which was later used in the INDIRECT function.

Creating 3 Lists based on Data Validation

Here we will create two worksheets. In the first worksheet, we will insert the information of 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 name range for each of the list. Select A2: A7 and write Dress in the Name Box. For range B2: B8 write Color and for range C2: C6 write Size.

Excel Data Validation Based on Another Cell

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

Excel Data Validation Based on Another Cell

  • After pressing OK. You will see a dropdown list 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 the Excel Data Validation. To perform this, select a range of cells and under the Data tab select Data Validation option.

In the Data Validation Dialogue box, select the Custom option as Validation Criteria. In the formula section, write =$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

Conclusion

In this article, we saw how to make lists using the 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

  1. Excel Index Match single/multiple criteria with single/multiple results
  2. INDIRECT Function Excel: Get values from different sheet
  3. Using VLOOKUP with IF Condition in Excel (5 Examples)

Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
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 attitude to grow continuously. Continuous improvement and life-long learning is my motto.

2 Comments
  1. Reply surja.gain@yahoo.com'
    Surya June 12, 2018 at 7:22 AM

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

    • Reply
      Siam Hasan Khan June 12, 2018 at 4:09 PM

      Thanks for your feedback, Surya.

    Leave a reply