Home > Validation > Dependent > Sorted List Excel Dependent Drop Down Lists from Sorted Table with OFFSETLimit 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. |
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.
Two worksheets are used in the sample workbook.
On the ValidationLists sheet, tables contain the lists that will be used in the data validation dropdowns:
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
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.
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:
To create the Region drop down list:
Select a Region from the drop down list. If no customer has been entered
in column C, the full list of regions is shown.
The next step is to create the dependent data validation dropdown lists in column C, for customer names.
=OFFSET(RegionStart, MATCH(B2, RegionColumn, 0) -1, 1, COUNTIF(RegionColumn,B2), 1)
We want the OFFSET function to return a reference to the range of cells that contains the East customers.
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.
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.
There two named cells on the ValidationLists sheet.
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))
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.
Create Dependent Drop Down Lists
Hide Previously Used Items in a Dropdown List
Last updated: October 31, 2022 7:29 PM