Written by Debra Dalgleish from Contextures Blog

With Excel data validation, you can create drop down lists in worksheet cells. Show the same list all the time, or show a “dependent” drop down  list, based on the value entered  in another cell. This video shows how to set one up, and there are written steps too.

Dependent Drop Down List Demo

First, here’s a quick dependent drop down list demo.  Select Fruit in one column, and the next column’s list shows fruit items. Select Vegetable, and the list is different.

Video: Dependent Drop Down List

Watch this video, see how to set up dependent drop down lists. The video timeline is below the video, to help you find a specific step in the setup process.

Video Timeline

Here’s the video timeline, to help you find a specific part of the video. The full video transcript is on my Contextures site.

• 00:00 Intro
• 00:39 Set Up the Worksheets
• 01:15 One-Word Names
• 01:41 Create Tables
• 02:55 Name the List Items
• 04:15 Main Drop Down List
• 05:48 Dependent Drop Down Lists

Dependent Drop Down List Setup

There are 3 lists used in this example – Produce Type, Fruit, Vegetable. The lists were typed on a blank worksheet, as shown below.

This is a simple example, and the main list – Product Type – has 1-word items only.

Tables and Named Ranges

Next, each list is formatted as a named Excel Table.

Then, the  items in each table are set as a named range.

• The main list is named Produce
• The other two lists have ranges named to exactly match the items in the Produce list – Fruit and Vegetable.

Add the Main Drop Down

On the data entry sheet, there are columns for Produce Type and Item.

A normal drop down list is added to the Produce Type column, based on the Produce named range.

Dependent Drop Down

Next, a dependent drop down list is added to the Item column. It will show either the Fruit or Vegetable list, based on what is selected in the Produce Type cell in that row.

Instead of a named range as its source, the dependent drop down uses the INDIRECT function in  a formula..

The INDIRECT function will return a reference to the range that is named in the Produce Type cell.

The dependent drop down is created in cell C3, in this example, and here is the formula used in the Data Validation source box:

=INDIRECT(B3)

Test the Dependent Drop Down

After you set up the dependent drop down cells, test them, to make sure things are working correctly.

• Select a Produce Type in cell B3
• Click the drop down arrow in cell C3
• The drop down list should show the items for the selected Produce Type

Get the Sample File

Go to my Contextures site, to get the dependent drop down sample file. That page also has detailed written steps for setting up the workbook.

Other Options for Dependent Drop Down

This example shows how to set up a simple set of drop downs, with one-word names used.

For more complex lists, or main lists with many items, there are other options for setting up dependent drop downs.

This video shows a flexible system designed by Roger Govier. Go to my Contextures site for the full details and sample file.

_____________________

Dependent Drop Down List in Excel

_____________________

_____________________

Dependent Drop Down List in Excel is a post from Contextures Blog and is not allowed to be copied to other sites

Original source: https://contexturesblog.com/archives/2019/10/10/dependent-drop-down-list-in-excel/