Multiple Dependent Drop Downs

Multiple Dependent Drop Downs in Excel http://contexturesblog.com/

With dependent drop downs, select an item from one Excel drop down list, and the next drop down only show the related items. For example, choose Canada in column C, and only see Canadian provinces in column D’s drop down. Now, go beyond those basics – see how to create multiple dependent drop downs – Region, Country, Area and City.

Video: Multiple Dependent Drop Downs

Thanks to Roger Govier, who shared this multiple dependent drop downs technique on my Contextures website, way back in 2014.

Multiple Dependent Drop Downs

Recently, I made a video that shows the steps for setting things up in a workbook, to complement Roger’s written steps.

Watch the video to see the steps, from beginning to end. You’ll find written instructions and the completed sample file on my website – Dependent Lists Using Tables.

Video Timeline

Here is a timeline that shows where each section of the video starts.

NOTE: The Named Range sections go into detail on how the range definition formulas work. You can skip those sections, if you prefer, and just copy the formulas from the website or sample file.

  • 00:00  Intro
  • 00:24  Source Lists
  • 01:41  Start the Workbook
  • 04:06  Add More Columns
  • 5:59  Named Ranges
  • 06:61  Named Range #1
  • 08:34  Named Range #2
  • 11:45  Named Range #3
  • 13:03  Drop Down Lists
  • 15:23  Get the Sample File

Quick Overview: Multiple Dependent Drop Downs

Here is a quick overview of how the multiple dependent drop downs are set up.

Create 2 Tables

  1. Create a data entry table with 4 columns – Region, Country, Area and City
  2. On another sheet, start a values table:
    • Type a list of items for the main list (Region)
    • Starting in the next column, type a country list for each Region – do not leave blank columns
    • Starting in the next column, type area and city lists.
    • Format all these Region/Country/Area/City list as a single formatted Excel table, named tblVal.

datavaldependtables04

Create 3 Named Ranges

Next, set up 3 named ranges –

  • _Regions – list of region names for the first drop down
  • _MainList – dynamic range will the full column for dependent list
  • _UseList – dynamic range with items for dependent list (no blanks)

The formulas for these named ranges are described in the video, and are shown on the instruction page, and in the sample file.

Add the Drop Down Lists

Finally, create data validation drop down lists in the Data Entry table.

  • The Regions column uses _Regions as its source list
  • Country, Area and City columns have _UseList as their source list

create data validation drop down lists

Get the Sample Workbook

The written instructions and completed sample file are on my website – Dependent Lists Using Tables.

There are two sample files to choose from:

  • one file has macros to automatically clear dependent cells if a higher level cell is changed
  • the other file has NO macros – you can clear the dependent cells manually, when necessary.

______________

Multiple Dependent Drop Downs in Excel http://contexturesblog.com/

2 thoughts on “Multiple Dependent Drop Downs”

  1. Thank you, this worked nicely in my spreadsheet with sample data. Here’s one catch: You may have the same selection items in 2 different categories and you cannot have columns with the same names.

    So in your example above, APAC may have Japan as a country (and then Japan would have a list of territories) but MEA may also have Japan as a country (with a different list of territories). When you make the column header for the 2nd instance of Japan, it will name the column “Japan2”

    So does this work only with unique selection items?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.