How do I change Data Validation list based on another cell?

Create Dependent Drop Down Lists

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, like Region and City lists. Videos, written steps, get free Excel file.

Table of Contents Show

Create Dependent Drop Down Lists
How to Create a Dependent Drop Down List in Excel
Data Validation using the INDIRECT Function based on Another Cell Value
Creating Main Dropdown List
Creating Dependent Dropdown List
Edit a drop-down list that’s based on a named range

What Is a Dependent Drop Down List?

Video: Dependent Drop Down Lists

Get Started: Dependent Drop Down Lists

Set up 2 sheets in the workbook
Create lists for the drop downs
Set up the main drop down list
Add the dependent drop down lists
Test the drop downs

Advanced Dependent Drop Downs

Use Two-Word Items
Use Illegal Characters
Add a 3rd Dependent Drop Down
Use Dynamic Source Lists
Dependent Drop Down IF Formula
Show Long or Short List

Prevent Invalid Selections

Download the Sample Files

How to Create a Dependent Drop Down List in Excel

-- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

Watch Video – Creating a Dependent Drop Down List in Excel

An Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards.

It shows a list of items as a drop down in a cell, and the user can make a selectionfrom the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.

Below is an example of an Excel drop down list:

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.

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.

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.

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 the name from the list Item which was later used in the INDIRECT function.

Edit a drop-down list that’s based on a named range

Select the worksheet that has the named range for your drop-down list.
Do any of the following:
- To add an item, go to the end of the list and type the new item.
- To remove an item, press Delete.
  
  Tip:If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.
Go to Formulas > Name Manager.
In the Name Manager box, click the named range you want to update.
Click in the Refers to box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.
Click Close, and then click Yes to save your changes.

Tip:If you don't know what a named range is named, you can select the range and look for its name in the Name Box. To locate a named range, see Find named ranges.