May 10th in Functions by Excelbud .

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.

Excelbud

Owner and site operator for Excelbuddy.com

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.

  1. Start with a list of categories. I chose to use State and cities for this demonstration.

    Validation List used to categorize data.

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

    list2

  3. Continue to define names for Ohio, Michigan, and Illinois. Highlight cells E3-E5 and type “Ohio” about column “A.”

    Continue to Name all columns.

  4. Once the columns are defined, we can now create our dependent validation list. Click on Cell A3.
  5. Click Data -> Data Validation ->Data Validation…
  6. Once the Data Validation box is displayed, click the down arrow and select “List.”

    data_validate-box

  7. Now it’s time to select the source data. The source data selected here will show “=State” but will be cells D3-D5.

    Source Data List

  8. Now we have a state list generated. Make sure your list is working correctly before moving to the next step.

    State drop-down list.

  9. Now comes the good stuff! Click on cell B3.
  10. Just like before, Click Data -> Data Validation ->Data Validation…
  11. Once the Data Validation box is displayed, click the down arrow and select “List.”
  12. Instead of clicking on the source button, type =INDIRECT(A3)

    =INDIRECT(A3)

  13. The INDIRECT function will take the selection in A3 and compare the names we defined earlier in B3.
  14. If you receive an error message stating “The Source currently evaluates do an error.” Click YES.

    final

  15. Please feel free to comment if you have any questions.

Tutorials Files:

data_validation

data_validation_97-2003

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

dependent-updated

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,” “,”"))

2-update

The files are available for download.

Dowload file for Excel 97-2003

Download file for Excel 2007

Related Posts

  • No Related Post

14 Comments

  • Bob
    May 11, 2009

    Great tutorial! I’ve been looking on how to do this for a long time.

  • YOUNUS
    August 13, 2009

    I HAVE NOT UNDERSTAND THE LAST STEP

  • Excelbud
    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!

  • Shaun
    September 8, 2009

    This doesnt help when I have 2 words separated by space in the lists. I am using Office 2007.

  • Excelbud
    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?

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

  • Michael George
    November 24, 2009

    excuse my horrible writing. All of your primary choices (states) are one word.

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

  • Excelbud
    November 24, 2009

    Thank you Michael. I misunderstood the original question posted by Shane. The explanation has been posted.

  • Martyn Cotmore
    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

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

  • melvin
    February 14, 2010

    Thanks tutorial added.

  • iwan
    July 7, 2010

    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?

  • Excelbud
    July 7, 2010

    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.

Leave A Comment.