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:

Explaining OFFSET function

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

Comments(2) Filed in category: Excel

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

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:

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

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.

Comments(12) Filed in category: Excel

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.

Comments(2) Filed in category: Count values, Excel

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 in excel

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

Comments(12) Filed in category: Excel, Named range

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 […]

Comments(12) Filed in category: Drop down lists, Excel, VLOOKUP and return multiple values

Download excel *.xlsx file