Home Tips Database Training Sample Spreadsheets
Excel -- Data Validation -- Create Dependent Lists
Create Named Lists
Apply the Data Validation
Test the Data Validation Using Two-Word Items
Using Items with Illegal Characters
You can limit the choices in a Data Validation list, by using named
ranges and the INDIRECT function.
In this example, if Fruit is selected as the Category, only Fruit
appears in the Item drop-down list.
Create Named Lists
Start by creating Named Lists, which will be the choices in the Data
Validation cells. In this example, the first list will be named Produce.
It contains the Produce categories -- Fruit and Vegetable.
1. Create the first Named List
a) In an empty area of the workbook, type the entries you want to see
in the drop-down list. These must be one-word entries, as they have to match
the dependent list names that will be created.
b) Select the cells in the list (but not the heading).
c) Click in the Name box, to the left of the formula bar
d) Type a one-word name for the list, e.g. Produce.
e) Press the Enter key.
2. Create the supporting Named Lists
a) Type the entries you want to see in the drop-down list for one of
the Produce categories.
b) Select the cells in the list.
c) Click in the Name box, to the left of the formula bar
d) Type a one-word name for the list, e.g. Fruit. This name must be
exactly the same as the matching entry in the Produce list.
e) Press the Enter key.
f) Create another list with the items for the next category --
Vegetable in this example.
Apply the Data Validation
The cells in the Category column will allow a List.
The cells in the Item column will use the INDIRECT function to select
a list..
1. Apply the Data Validation
a) Select the cells in which you want to apply data validation using
the Category List
b) From the Data menu, choose Validation.
c) From the Allow drop-down list, choose List
d) In the Source box, type an equal sign and the list name, for
example: =Produce
e) Click OK.
2. Create the Dependent Data Validation
a) Select the cells in which you want to apply data validation using
the Fruit or Vegetable List, dependent on which Category has been selected
b) From the Data menu, choose Validation.
c) From the Allow drop-down list, choose List
d) In the Source box, type an equal sign and INDIRECT function,
referring to the first data cell in the Category column: =INDIRECT(A2)
e) Click OK.
Test the Data Validation
Cells in the Category column will display items in the Produce List.
Cells in the Item column will show items from the Fruit or Vegetable
List, depending which has been selected in the Category column
Using Two-Word Items
You may need to have two-word items in the first drop-down list. For
example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit'
Create the first named range and dropdown list as described above.
Create the supporting named lists, using one-word names, e.g.
RedFruit, GreenFruit, YellowFruit
For the second dropdown, choose to Allow: List, and use a formula that
removes the spaces from the names. For example:
=INDIRECT(SUBSTITUTE(A2," ".""))
Using Items with Illegal Characters
You may need to have items in the first drop-down list that contain
characters not allowed in range names, such as the ampersand (&). For
example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow & Orange
Fruit'
Create the first named range and dropdown list as described above.
Create the supporting named lists, using one-word names, e.g.
RedFruit, GreenFruit, YellowOrangeFruit
Create a lookup table that contains the names from the first dropdown
list.
In the adjacent cell, enter a valid range name
Name this table, eg NameLookup
For the second dropdown, choose to Allow: List, and use a formula that
looks up the valid name. For example:
=INDIRECT(VLOOKUP(A2,NameLookup,2,0))
1. Data Validation
3. Data Validation -- Hide Previously Selected Items
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
To download a sample file, click here: Data Validation Sample
Home Tips Database Training Sample Spreadsheets
Contact: (e-mail address removed)
sarah said:
I have a drop down menu on sheet 1 with 4 options. I have 4 other sheets
with the corresponding information for each of those options. How can I get
Excel to either when the user selects from the menu which option they
require to show the information they need on sheet 1 or take them to the
page with the option?
