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)

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))

Array formula in cell G2:

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

How to create an array formula

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

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)))

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.

Download excel *.xlsx file