Search Contextures Sites
Custom Search


More Tutorials

Data Validation Basics

Dependent Drop Downs

Dependent Lists With INDEX  




Excel Data Entry Popup List






Dependent Dropdowns from a Sorted List

Limit the choices in an Excel drop down list, based on the value in another cell. These instructions are for Excel 2003 and earlier. For Excel 2007, and later versions, click here.

Set up the Workbook   

Create a Dropdown List    

Create a Dependent Dropdown List   

Test the Validation  

Download the Sample File

More Data Validation Tutorials


You can limit the choices in an Excel Data Validation list, by using named ranges and the INDIRECT function, as explained here: Data Validation -- Create Dependent Lists

Another method is to use the OFFSET function, to extract items from a sorted list, as described below. In this example, a region is selected in one column, and the customers in that region will appear in the data validation list in the adjacent cell.

Set up the Workbook

Two worksheets are required in this workbook.

  1. Delete all sheets except Sheet1 and Sheet2
  2. Rename Sheet1 as ValidationSample
  3. Rename Sheet2 as ValidationLists

On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:

  1. In cells A1:B20 type a list of Regions and Customers
    Note: This list must be sorted by Region
  2. In cells D1:D4 type a list of Regions

Name the following ranges (there are Naming instructions here: Name a Range):

  1. Name cell A1 as RegionStart
  2. Name column A as RegionColumn
  3. Name column B as CustColumn
  4. Name range D2:D4 as RegionList    

Create a Data Validation Dropdown List

On the ValidationSample sheet, type the headings Region and Customer, in cells B1 and C1.

The next step is to create the Region data validation dropdown lists in column B.

  • Cells B2:B10 have data validation lists with the source RegionList. When a cell in this range is selected, a dropdown list of Regions is available.
  • The formula for the list is:
  • The complete region list is shown if no customer has been selected. However, if a customer has been selected in the adjacent cell, only that customer's region is shown in the Region dropdown list.

There are detailed instructions for creating data validation lists here: Data Validation -- Introduction   

Create a Dependent Dropdown List

The next step is to create the dependent data validation dropdown lists in column C.

  1. In cell B2, select Ontario from the dropdown list. (If the cell is left empty, an error message may occur, when creating the dependent validation in column C.)
  2. Select cells C2:C10
  3. Choose Data | Validation
  4. From the Allow dropdown, choose List
  5. In the Source box, type the following formula:


The OFFSET function has the following arguments:

We want the OFFSET function to return a reference to the range of cells that contains the Ontario customers.
Reference:  In our formula, the reference is RegionStart, cell A1 on the ValidationLists sheet.
Rows:  How many rows down from the reference cell should our range start? The MATCH function finds the first instance of Ontario in the RegionColumn, in row 6. We subtract 1 from this number, because the starting cell is in row 1.
Columns:   We want a range that is 1 column to the right of the RegionStart reference.
Height:  The COUNTIF function counts the number of times that region is entered in the RegionColumn. There are 9 customers in the Ontario region.
Width:  We want a range that is 1 column wide

  1. Click OK   

Test the Validation

  1. Select cell C2
  2. Click the data validation dropdown arrow
  3. A list of Ontario customers is displayed.

Download the Sample File

Download the zipped sample file

More Data Validation Tutorials

Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List  
Data Validation - Dependent Lists With INDEX  
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box 
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box     
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items  



Privacy Policy


Contextures Inc., Copyright 2016
All rights reserved.



Last updated: April 6, 2016 4:44 PM