Home > Validation > Dependent > Sorted List Excel Dependent Drop Down Lists from Sorted Table with OFFSET Limit the choices in a Microsoft Excel drop down list, so it only shows items related to the selection in another cell. Select a region, and choose from a list of customers in that region.

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.

NOTE: For Excel 2003 instructions (no named Excel Tables), click here.

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.

If you have a long list of items, I recommend this OFFSET function method, -- 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)

Name the Ranges

Next, create four named ranges on the spreadsheet, based on the cell address and names shown in the list below.

Tip: A quick way to name a selected cell range is by typing a one-word name in the Name Box, to the left of the Formula bar, then press the Enter key. There are step-by-step 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. Each named range will adjust automatically, if you add rows or remove rows in the Tables.

Create a Region Drop Down 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 column B. In this drop down:

• The complete region list will be shown if no customer has been selected in the adjacent cell.
• After a Customer is selected, the Region dropdown will only show the currently selected region name.

To create the Region drop down list:

• Select the region cells (not the heading), and on the Ribbon, click the Data tab
• Click Data Validation, to open the Data Validation dialog box.
• On the Settings tab, click the drop down arrow on the Allow box
• In the list of options, select List
• In the formula box, type this formula:
  =IF(C2="",RegionList,B2)
• Click OK, to close the dialog box

Select a Region from the drop down list. If no customer has been entered in column C, the full list of regions is shown.   

Create a Dependent Dropdown List

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

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 the customer cells (not the heading), 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)

6. The OFFSET function has the following arguments:

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

7. 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.
4. Select one of the customers



5. Next, select cell B2, and click the drop down arrow
6. Because a customer has been selected, only the East region is in the list

Check the List Sort

This technique depends on the source list being sorted by the Region column. If the list is not sorted A-Z by region, an incorrect customer list might be shown. To ensure that the list is correctly sorted, the following optional formulas can be used.

NOTE: These formulas are shown in download file #2, in the Download section, below.

Check Sort Order

In the Region/Customer table, a third column is added, with this formula, to check the sort order:

=IF(A3="",0,--(A3<A2))

If the items are in order, the result in each row will be zero. If an item is out of order, the formula returns a 1, in the row above that item.

In this screen shot, East is out of order in row 5, so a 1 is the formula result in cell C4.

Sort Check Total and Message

There two named cells on the ValidationLists sheet.

• SortCheck: Contains a formula to calculate the total in the SortCheck column
  • =SUM(tblRegCust[SortCheck])
• SortMsg: Contains a typed message that will be shown when the list is not sorted correctly.

Customer Drop Down List

On the ValidationSample sheet, the customer data validation formula is changed slightly.

=IF(SortCheck>0, SortMsg, OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1))

• The formula checks the SortCheck cell, to see if it is greater than zero.
• If so, the SortMsg range is shown, instead of the customer names.

To see the customer list, go to the ValidationList sheet, and sort the table by the Regions column.

Then, click the drop down arrow, to see the customers for the selected region.

Get the Sample Files

1. Get the sample file for dependent drop down from sorted list. The zipped flle is in xlsx format, and does not contain macros.
2. Get a sample file that includes formulas to check if the source list is correctly sorted. The zipped flle is in xlsx format, and does not contain macros.
3. Get a sample file with 3 levels of dependent drop downs - Region, Sales Rep and Customer. The zipped flle is in xlsx format, and does not contain macros.

More Tutorials

Data Validation Basics

Create Dependent Drop Down Lists

Dependent Lists With INDEX  

Hide Previously Used Items in a Dropdown List

Use a List from Another Workbook

Data Validation Tips