Dependent Drop Down Lists in Excel

In Excel, you can set up drop down lists that are dependent on the selection made in another cell. In this example, you select a region in column B, and only the customers in that region are in the drop down list in column C.

datavaldependsort

Use OFFSET not INDIRECT

One way to accomplish this is with named ranges and the INDIRECT function, as explained here: Data Validation — Create Dependent Lists. That method works well if there are only a few options in the first column.

For a longer list of items, it might be difficult, or impossible, to set up all the named ranges that you need, and to maintain all those lists.

To make things easier, this tutorial uses the OFFSET function, to extract related items from a sorted list.

datavaldependsort07

The only restriction is that the main column has to be sorted, so that all the items are grouped together.

datavaldependsort02

Updated for Excel Tables

The original version of this tutorial used lists on the worksheet, and I have updated it to use named Excel tables. Now, when you name the ranges, they are connected to the tables, so they adjust automatically if the table size changes.

datavaldependsort03

Cross Validation

The Region column uses a simple list as the source for its drop down, as long as no customer has been selected. However, if a customer name is in column C, the Region drop down only shows the region for that customer.

If you want to start over, clear out both cells in the row, and select a region, then a customer.

datavaldependsort04

Download the Sample File

To see the detailed instructions, and to download the sample file, please visit my Contextures website: Dependent Drop Downs from a Sorted List. The sample file is in xlsx format, and does not contain macros.

You can find the Excel 2003 version here: Dependent Drop Downs 2003 Sorted List

__________________

You may also like...

1 Response

  1. ZachAttack17 says:

    Can ‘Ed’ work in ‘East’ & ‘Central’?

    If I choose ‘East’ I can see ‘Ed’ and ‘Gary’

    1) I want to choose ‘Central’ and still see ‘Ed’ but not ‘Gary’.
    2) I want to choose ‘Ed’ and only see ‘East’ and ‘Central’, but NOT ‘West’.

Leave a Reply to ZachAttack17 Cancel reply

Your email address will not be published. Required fields are marked *