Data Validation – Dependent Lists
This tutorial will show you how to create a dependent list on Excel. This technique is extremely useful when categorizing data. In the example below, the user will select the State in cell A3. Cell B3 (City) will pull a city based off of the users selection.
This tutorial will show you how to create a dependent list on Excel. This technique is extremely useful when categorizing data. In the example below, the user will select the State in cell A3. Cell B3 (City) will pull a city based off of the users selection.
- Start with a list of categories. I chose to use State and cities for this demonstration.
- In order to set up our validation list, we need to define names for each series. To begin, select cells D3-D5. Right above column A, type “State” and hit enter.
- Continue to define names for Ohio, Michigan, and Illinois. Highlight cells E3-E5 and type “Ohio” about column “A.”
- Once the columns are defined, we can now create our dependent validation list. Click on Cell A3.
- Click Data -> Data Validation ->Data Validation…
- Once the Data Validation box is displayed, click the down arrow and select “List.”
- Now it’s time to select the source data. The source data selected here will show “=State” but will be cells D3-D5.
- Now we have a state list generated. Make sure your list is working correctly before moving to the next step.
- Now comes the good stuff! Click on cell B3.
- Just like before, Click Data -> Data Validation ->Data Validation…
- Once the Data Validation box is displayed, click the down arrow and select “List.”
- Instead of clicking on the source button, type =INDIRECT(A3)
- The INDIRECT function will take the selection in A3 and compare the names we defined earlier in B3.
- If you receive an error message stating “The Source currently evaluates do an error.” Click YES.
- Please feel free to comment if you have any questions.
Tutorials Files:
Updated Tutorial – 11/24/2009
Creating a Dependent List for two-word items
The question has been asked how to make a dependent list when the primary choice is more than one word.
In the example below, “Buckeye Nation” is the two letter state. I realize this is not a State, but name a US State that has two words.
Next, create the supporting list using a one word title. Instead of writing “Buckeye Nation” use “BuckeyeNation.” This is shown in Column G
Now, follow Step 2 above to Define the list for cells G3-G4. Make sure you use a one word title eg. “BuckeyeNation”
In order to remove the space, we must utilize the formula below. Before this question was asked by visitors of Excelbuddy.com, I was unfamiliar myself on how this could be done. However, I did a Google search and was able to find a solution.
=INDIRECT(SUBSTITUTE(A2,” “,”"))
The files are available for download.
Dowload file for Excel 97-2003
Great tutorial! I’ve been looking on how to do this for a long time.
I HAVE NOT UNDERSTAND THE LAST STEP
I see what your saying in terms of the last step. I will fix the explanation to make it more clear. Thank you for pointing it out Younus!
This doesnt help when I have 2 words separated by space in the lists. I am using Office 2007.
Hi Shane,
The example above has two words separated. It should work for you.
Can you show us what you are trying to do?
What shane is saying is that if you have a space that you cannot name a range anything that has a space in it. This is a problem if one of your list choices has a space in it and you need to make a dependent list dependent on that choice. You DO NOT have this in your example because all of your primary lists are one word.
excuse my horrible writing. All of your primary choices (states) are one word.
This is because you range references cannot have spaces in the name. Excel recommends using the underscore such as School_bus if you want to separate words.
Thank you Michael. I misunderstood the original question posted by Shane. The explanation has been posted.
I’ve created a dependent drop down list using the Indirect function where the first column list drops down to reveal counties and the dependent list drops down to reveal major cities within them.
However, when I copy and paste the dependent list to the row below the second validation data source does not up-date to the new row number. I don’t want to have to physically change each reference because the worksheet is some 2500 rows long. Can you help?
Thanks,
Martyn
Hi Martyn,
Use the Auto Fill function in Excel. Use the link below or search Auto Fill above.
http://excelbuddy.com/auto-fill/
Simply highlight the cells and use the fill handle.
Bryan
Thanks tutorial added.
hello, to display a list of validation data we must use a mouse, can we use the keyboard to display the list in data validation?
You can use the keyboard to move a highlighted box around the spread sheet. Once you have a particular cell selected, hit “Enter.”
This is really the only way to use your keyboard.
Is there a solution for lists with numbers.
I have 3 lists:
1. Hight
2. Width
3. Price
Regards
How do we configure for 3 values.. Country, city and state.
Hi,
I’ve created names dynamically (and not with the little box on the top left)
formulas -> name manager -> refers to
=OFFSET($E$2, 0, 0, (COUNTA($E:$E) – 1), 1)
and now the dependent drop down list does not work, any solutions?
thank you
I’m not exactly sure. I’ll have to look into it. I’m not familiar creating names dynamically. I tend to stick with entering names one by one when using dependent lists.
Hi,
In Excel 2007 you can define names more easily. Select whole table showing the name of states and cities of the state as shown in your example and click “Formulas Toolbar” and click create from selection, check “Top Row” and hit “Ok”. All the name ranges will be defined automatically.
Regards,
Zaigham
Thank you Zaigham for explaining how to define names in Excel 2007.
Excelbud.
Got it. Have to click “Apply these cvhanges to all other cells with the same settings”
How do I get it to reset that second column. In other words, Column A is Cars, Boats, Trains. Column B has derivatives of column A (mustang, corvette, etc for cars, submarine, carrier, etc. for boats, and Electric, deisel, steam, etc for trains.)
When I selected Column A – Cars, Column B Mustang I later realized I wanted Column A to be Trains, Colun B to be Steam. Unfortunately, it’s possible to change column A (cars), but Column B doesn’t change (leaving it Mustang).
Help would be appreciated.
Hi Casey
Column B will not be changed automatically with reference to change in column A. You must have to click column B and select required item.
If I understand correctly, after clicking column A and selecting “Trains”, have you clicked column B to select from the drop down list “Steam”.
This tutorial did the trick.
For future reference, 20% of the states have 2 words in their names:
New Hampshire
New Jersey
New Mexico
New York
North Carolina
North Dakota
Rhode Island
South Carolina
South Dakota
West Virginia
Hi Bryan,
Many thanks to your great tutorial!
But what if the dependent list is on another workbook. Can we do it as well?
May
I tried this tutorial and it works only when i download the excel sheet from the site. Why is it when i do it on my own it does not seem to work? Can you please help me out on this
Love the tutorial – wondering how I can add one more dependency: What if you wanted a store, in a city, in a state. The city is dependent on the state, and the store is dependent on the city. Using the knowledge from this tutorial, I’ve experimented around, and can’t figure it out. Can it be done?
I’ve been working on a worksheet with data validation lists but I’ve run into a problem. What if you have 2 cells dependent on 1? Say, for example both B3 and D3 depend on the value of A3.