Josh asks:

now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set.

Answer:

Table of contents

Overview
Create auto expanding named ranges
Array Formula on Calculation sheet
Create another two auto expanding named ranges on sheet "Calculation"
Create Combo boxes
Setup Combo boxes
Array Formula on Filter sheet
Download example workbook

Overview

Filter or an excel table can accomplish this task within milliseconds but several people have asked how to do this using drop down lists.

So in this post I am going to use dependent combo-boxes instead of drop down lists.We are not going to use any vba code but we need to create a "calculation" sheet.

Why use combo box? If  you decide to change a value in a  combo box, other dependent combo box values change instantly. This is not the case with drop down lists.

We are going to create two combo boxes. The first combo box contains unique distinct values from column A. The second combo box contains unique distinct values from column B, based on selected value in the first combo box. See pictures below.

The data we are working with:

The final result:

If you want to create drop down lists read this article: Create dependent drop down lists containing unique distinct values in excel and then continue reading this article.

What is a unique distinct list? Remove all duplicates in a list and you have create a unique distinct list.

I have created three sheets.

  • Filter
  • Data
  • Calculation

Create auto expanding named ranges

We are going to create two named ranges, auto expanding when new values are added. The two named ranges exists on sheet "Data"

Named range "order"

  1. Click "Formulas" tab
  2. Click "Name Manager"
  3. Click "New..."
  4. Type a name. I named it "order". (See attached file at the end of this post)
  5. Type =OFFSET(Data!$A$3, 0, 0, COUNTA(Data!$A$3:$A$1000)) in  "Refers to:" field.
  6. Click "Close" button

Named range "product"

  1. Click "Formulas" tab
  2. Click "Name Manager"
  3. Click "New..."
  4. Type a name. I named it "product". (See attached file at the end of this post)
  5. Type =OFFSET(Data!$B$3, 0, 0, COUNTA(Data!$B$3:$B$1000)) in  "Refers to:" field.
  6. Click "Close" button


Array Formula on sheet "Calculation"

Array formula in cell A2:

=IFERROR(INDEX(order, MATCH(0, COUNTIF($A$1:A1, order), 0)), "")

Press CTRL + SHIFT + ENTER. Copy cell A2 and paste it down as far as needed.

Array formula in cell B2:

=IFERROR(INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(order<>INDEX(Calculation!$A$2:$A$3, Filter!$B$1)), 0)), "")

Press CTRL + SHIFT + ENTER. Copy cell B2 and paste it down as far as needed.

The values in column B change depending on selected value in the combo box on sheet "Filter". Don´t worry, we are soon going to create the combo boxes.

Create another two auto expanding named ranges on sheet "Calculation"

The named ranges auto expands when lists change in column A and B.

Named range "uniqueorder"

  1. Click "Formulas" tab
  2. Click "Name Manager"
  3. Click "New..."
  4. Type a name. I named it "uniqueorder". (See attached file at the end of this post)
  5. Type =OFFSET(Calculation!$A$2,  0,  0,  COUNT(IF(Calculation!$A$2:$A$1000="",  "",  1)),  1) in  "Refers to:" field.
  6. Click "Close" button

Named range "uniqueproduct"

  1. Click "Formulas" tab
  2. Click "Name Manager"
  3. Click "New..."
  4. Type a name. I named it "uniqueproduct". (See attached file at the end of this post)
  5. Type =OFFSET(Calculation!$B$2,  0,  0,  COUNT(IF(Calculation!$B$2:$B$1000="",  "",  1)),  1) in  "Refers to:" field.
  6. Click "Close" button

Create Combo boxes

  1. Click "Developer" tab on the ribbon
  2. Click "Insert" button
  3. Click Combo box button
  4. Place combo box on cell B2 on sheet "Filter

 

Create a second combo box on cell C2.

 

Setup Combo box 1

  1. Select sheet "Filter"
  2. Right click on combo box on cell B2
  3. Click "Format Control..."
  4. Click tab "Control"
  5. Type uniqueorder in Input range:
  6. Select cell B1 in Cell link:
  7. Click OK

Setup Combo box 2

  1. Select sheet "Filter"
  2. Right click on combo box on cell C2
  3. Click "Format Control..."
  4. Click tab "Control"
  5. Type uniqueproduct in Input range:
  6. Select cell C1 in Cell link:
  7. Click OK

I have hidden the values in cell B1 and C1.

  1. Select B1:C1
  2. Press Ctrl + 1
  3. Click "Custom" in Category window
  4. Type ,,, in the "Type:" field
  5. Click OK

Array Formula on "Filter" sheet


Array formula in cell B7:

=IFERROR(INDEX(Data!$A$3:$C$11, SMALL(IF((INDEX(uniqueorder, $B$1)=order)*(INDEX(uniqueproduct, $C$1)=product), ROW(order)-MIN(ROW(order))+1, ""), ROW(A1)), COLUMN(A1)), "")

Press CTRL + SHIFT + ENTER. Copy cell B7 and paste it to the right to cell B9. Copy cell range B7:B9 and paste it down as far as needed.

Download example workbook
combo box unique-distinct-dependent-lists2.xlsx
(Excel 2007  Workbook *.xlsx)

Related articles:

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Create dependent drop down lists containing unique distinct values in excel

Functions in this article:

IF(logical_test,[value_if:true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

ROW(reference)
Returns the rownumber of a reference

SMALL(array,k)
Returns the k-th smallest row number in this data set.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text