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 dependent drop down list in column C.
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.
Sort Main Column
The only restriction is that the main column has to be sorted, so that all the items are grouped together.
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.
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.
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
Video: Make a Dependent Drop Down List
To see how to make a basic dependent drop down list in a cell, watch the steps in this short video, and the written instructions are on the Excel Dependent Drop Down List page on my Contextures site.
__________________
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’.