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.
Contents
Demo
Video
Setup
Tables
Main Drop Down
Dependent Drop Down
Testing
Get the Sample File
Other Options
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
_____________________
_____________________