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
May 11, 2009
Great tutorial! I’ve been looking on how to do this for a long time.
August 13, 2009
I HAVE NOT UNDERSTAND THE LAST STEP
August 13, 2009
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!
September 8, 2009
This doesnt help when I have 2 words separated by space in the lists. I am using Office 2007.
September 8, 2009
Hi Shane,
The example above has two words separated. It should work for you.
Can you show us what you are trying to do?
November 24, 2009
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.
November 24, 2009
excuse my horrible writing. All of your primary choices (states) are one word.
November 24, 2009
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.
November 24, 2009
Thank you Michael. I misunderstood the original question posted by Shane. The explanation has been posted.
December 7, 2009
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
December 8, 2009
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
February 14, 2010
Thanks tutorial added.