Search Contextures Sites

 

Contextures
Excel news
by email

 

30 Excel Functions in 30 Days

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Dependent Dropdowns from a Sorted List

These instructions are for Excel 2007, and later versions. For Excel 2003 instructions, click here.

Dependent Drop Down Lists

You can limit the choices in a drop down list, so that it only shows items related to the selection in another cell.

In this example, a region is selected in column B, and only the customers in that region will appear in the drop down list in column C.

dependent drop down lists

This tutorial uses the OFFSET function, to extract related items from a sorted list.

Another method, which uses named ranges and the INDIRECT function, is explained here: Data Validation -- Create Dependent Lists. That method works well if there are only a few options in the first column.

I recommend this OFFSET function method, if you have a long list of items -- it is easier to set up and maintain.

Set up the Workbook

Two worksheets are used in the sample workbook.

  1. Data entry is set up on the ValidationSample sheet
  2. Region and Customer lists are set up on the ValidationLists sheet

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

  1. In cells A1:B20 there is a list of Regions and Customers
    Note: This list must be sorted by Region
  2. In cells D1:D4 there is a list of Regions
  3. Both lists have been formatted as named Excel tables (Insert tab, Table command)

region customers lists

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

  1. Name cell A1 as RegionStart
  2. Name cells A1:A20 as RegionColumn
  3. Name cells B1:B20 as CustColumn
  4. Name cells D2:D4 as RegionList    

NOTE: If you look in the Name Manager after creating these names, you will see that they all refer to the named Tables. These named ranges will adjust automatically, if rows are added or removed in the Tables.

region customers lists

Create a Region Dropdown List

On the ValidationSample sheet, drop down lists are created for the Region and Customer columns. The data entry area is also a named Excel table, and it will expand automatically, with drop down lists appearing in the new rows.

The first step is to create the Region drop down list in cell B2. 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.

dependent drop down lists

To create the Region drop down list:

  • Select cell B2, and on the Ribbon, click the Data tab
  • Click Data Validation, to open the Data Validation dialog box.
  • From the Allow drop down, select List
  • In the formula box, type this formula:
    =IF(C2="",RegionList,INDEX(RegionColumn,MATCH(C2,CustColumn,0)))
  • Click OK, to close the dialog box

dependent drop down lists

Select a Region from the drop down list. No customer has been entered in column C, so you should see the full list of regions.   

dependent drop down lists

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 East 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 cell C2, and on the Ribbon, click the Data tab
  3. Click Data Validation, to open the Data Validation dialog box.
  4. From the Allow drop down, select List
  5. In the formula box, type this formula:

    =OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1,1,COUNTIF(RegionColumn,B2),1)

The OFFSET function has the following arguments:

dependent drop down lists

We want the OFFSET function to return a reference to the range of cells that contains the East 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 East in the RegionColumn, in row 8. We subtract 1 from this number, because the starting cell is in row 1.
  • Cols:   How many columns away from the starting range? We want a range that is 1 column to the right of the RegionStart reference.
  • Height:  How many rows in the selected range? The COUNTIF function counts the number of times that region is entered in the RegionColumn. There are 6 customers in the East region.
  • Width:  How many columns in the selected range? We want a range that is 1 column wide
  1. Click OK, to close the dialog box   

Test the Validation

  1. Select cell C2
  2. Click the dropdown arrow
  3. A list of the East region customers is displayed.

dependent drop down lists

Download the Sample File

Download the zipped sample file. The flle is in xlsx format, and does not contain macros.

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  

 

Learn how to create Excel dashboards.

 

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.

 

 

Last updated: August 18, 2013 8:59 AM