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

Formula:

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

Learn more about the OFFSET and MATCH function:

Explaining OFFSET function

In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]

How to use Excel’s MATCH function

Identify the position of a value in an array.

Name: Region

Formula:

=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:

INDEX function explained

Fetch a value in a data set based on coordinates.

Array formula in cell G2:

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

Learn more about SMALL function:

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

How to create an array formula

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

Learn the basics of Excel arrays

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

Formula:

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

Name: UniqueProduct

Formula:

=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

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns […]

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

Dependent-Data-validation-lists-in-multiple-rows1.xlsx