Author: Oscar Cronquist Article last updated on August 29, 2017

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent column. There is no vba code in the example demonstrated here.

Named ranges

The following named ranges expands automatically when new text values are added.

Name: Product


=OFFSET('Dependent values'!$A$2, 0, 0, MATCH("ZZZZZZZZ", 'Dependent values'!$A:$A)-1)

Learn more about the OFFSET and MATCH function:

How to use the OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

How to use the MATCH function

Identify the position of a value in an array.

Name: Region


=OFFSET('Dependent values'!$B$2, 0, 0, MATCH("ZZZZZZZZ", 'Dependent values'!$B:$B)-1)

Array fomulas

The first array formula returns unique distinct values in cell G1:L1 from named range Region. The second array formula in cell range G2:L7 returns coherent values from each region. All of these calculations can be hidden or placed on a calculation sheet.

Array formula in cell G1:

=INDEX(Region, MATCH(0, COUNTIF($F$1:F1, Region), 0))

Learn more about INDEX function:

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

Array formula in cell G2:

=INDEX(Product, SMALL(IF(G$1=Region, MATCH(ROW(Region), ROW(Region))), ROW(A1)))

Learn more about SMALL function:

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

How to create an array formula

  1. Select a cell
  2. Type array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

Copy cell G1 and paste to cell range H1:L1. Copy cell G2 and paste to cell range G2:L7.

Named ranges

Now it is time to create the two last named ranges that will be used in data validation lists.

Name: UniqueRegion


=OFFSET('Dependent values'!$G$1, 0, 0, 1, MATCH("ZZZZZZZ", 'Dependent values'!$G$1:$L$1))

Name: UniqueProduct


=OFFSET('Dependent values'!$G$2;0;MATCH('Dependent values'!$D2;'Dependent values'!$G$1:$L$1;0)-1;SUMPRODUCT(--('Dependent values'!$D2=Region)))

Create a dynamic named range

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Data validation lists

  1. Select cell range D2:D9
  2. Go to tab "Data"
  3. Click "Data validation" button
  4. Select "List"
  5. Type =UniqueRegion
  6. Click OK

Repeat above steps with cell range E2:E9 and Source: =UniqueProduct.

This article shows you how to search using a drop down list:

Use a drop down list to search and return multiple values

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]

Download excel *.xlsx file