Author: Oscar Cronquist Article last updated on June 17, 2022

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

Answer:

1. 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

Back to top

2.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. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "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. Press with left mouse button on "Close" button

Named range "product"

  1. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "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. Press with left mouse button on "Close" button

Back to top

3. 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.

Back to top

4. 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. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "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. Press with left mouse button on "Close" button

Named range "uniqueproduct"

  1. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "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. Press with left mouse button on "Close" button

Back to top

5. Create Combo boxes

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

 

Create a second combo box on cell C2.

 

Back to top

5.1 Setup Combo box 1

  1. Select sheet "Filter"
  2. Press with right mouse button on on combo box on cell B2
  3. Press with left mouse button on "Format Control..."
  4. Press with left mouse button on tab "Control"
  5. Type uniqueorder in Input range:
  6. Select cell B1 in Cell link:
  7. Press with left mouse button on OK

Setup Combo box 2

  1. Select sheet "Filter"
  2. Press with right mouse button on on combo box on cell C2
  3. Press with left mouse button on "Format Control..."
  4. Press with left mouse button on tab "Control"
  5. Type uniqueproduct in Input range:
  6. Select cell C1 in Cell link:
  7. Press with left mouse button on OK

I have hidden the values in cell B1 and C1.

  1. Select B1:C1
  2. Press Ctrl + 1
  3. Press with left mouse button on "Custom" in Category window
  4. Type ,,, in the "Type:" field
  5. Press with left mouse button on OK

Back to top

6. 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.

Back to top

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