## Apply dependent combo box selections to a filter in excel 2007

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

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"*

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

*Named range "product"*

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

**Array Formula on sheet "Calculation"**

Array formula in cell A2:

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

Array formula in cell B2:** **

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"*

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

*Named range "uniqueproduct"*

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

- Click "Developer" tab on the ribbon
- Click "Insert" button
- Click Combo box button
- Place combo box on cell B2 on sheet "Filter

Create a second combo box on cell C2.

- Select sheet "Filter"
- Right click on combo box on cell B2
- Click "Format Control..."
- Click tab "Control"
- Type
*uniqueorder*in Input range: - Select cell B1 in Cell link:
- Click OK

**Setup Combo box 2
**

- Select sheet "Filter"
- Right click on combo box on cell C2
- Click "Format Control..."
- Click tab "Control"
- Type
*uniqueproduct*in Input range: - Select cell C1 in Cell link:
- Click OK

I have hidden the values in cell B1 and C1.

- Select B1:C1
- Press Ctrl + 1
- Click "Custom" in Category window
- Type ,,, in the "Type:" field
- Click OK

**Array Formula on "Filter" sheet**

Array formula in cell B7:

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

That's great stuff. However this formula breaks when 3rd OrderID is put in place than $A$2:$A$3 wouldn't be enough anymore.

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

$A$2:$A$4 doesn't work.

Any ideas?

John,

That is strange. I have added another Order ID (Sheet Data) and changed the cell reference to Calculation!$A$2:$A$15. It works perfectly here.

Download my file.

Hi Oscar

Right now I am thinking that you are an angel, I have been trying to work out how to do this for along time, so thank you for your blogs.

I have taken what you have done and tried to adapt it to my requirements which have 10 dropdowns/combo boxes, with no luck, is it possible to have multiple inputs?

Each combo box needs to consider the previous ones, always in the same sequence.

Thx for your help

Nicole,

How many combo boxes are working in your spreadsheet?

Send your workbook without sensitive information with this contact form

John,

It's just a neat trick to set you right.. after you edit the formula, press ctrl+shift+enter !!!! arrays !!!

Wonderful job Oscar. Thanks a ton for that brilliant weave of logic!

Everyone,

Please do go through Oscar's other tutorial

http://www.get-digital-help.com/2010/10/28/apply-dependent-combo-box-selections-to-a-filter-in-excel-2007/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/

He has explained every bit of his process as explicitly as one can! and don"t forget to thank him :)

Hi,

I found a few errors with the Array Formulas.

Array formula in cell B2:

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

The last part should be Filter!$B$2 instead.

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

The $B$1 and $C$1 should both be $2 instead of $1.

anyhow thanks for your tutorial helped me a lot!

Derek

Derek Cheng,

Thank you!

The cell references are in fact correct!

If you the open attached excel file, you will find that values in cell B1 and C1 are hidden using formatting ;;;

http://office.microsoft.com/en-us/excel-help/hide-or-display-cell-values-HP005255043.aspx

The comboboxes are linked to cell B1 and C1.

Hi,

how can you handle the case, if you select an order Id from the first dropdown and you would like to have "all" from product category, similar to a pivot where you can select "all"

Any idea,

Br, Mick

Mick,

I believe this post has an answer to your question:

Dependent drop down lists – Enable/Disable selection filter

I also want to do this but i can't find a way of combine those 2.

I want to be able to select all and every product in that order will apear and only be dependent if I choose a specific product.

Is that easy to do?

hi,

i tried it and it works..

but how is it done if instead of 2 combo box, i want it 3 (3 categories to choose from).

thanks..

I am having trouble adapting this for three combo boxes. If the second array formula is:

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

How would I do a third array that refers back to both the previous two? I know what I want to do, I just don't know how to code it.

By the way, THANK YOU! This has been unbelievably helpful.

Dan,

I tried using array formula for the same problem but i ended up using pivot table. It took me seconds to get what i wanted.

bryan,

Yes, but you have to manually extract unique values from the pivot table. That is fine if you use the same cell range all the time.

The example in this post handles expanding cell ranges.

Dan,

Download excel file:

Dan-combo-box-unique-distinct-dependent-lists2.xlsx

Hi Oscar,

Excellent explanations and sample files. I have a 2 part question.

Part 1

I'm trying to use this combo box method to help a restaurant owner have a quick and user friendly template to fill out purchase orders to send to food suppliers. I have it setup for Category (dairy, meat, produce) then product (cheese, bacon, watermelon) then price.

I am getting stuck adding to the data tabs etc like John back in the April comments. I mess around with changing the cell references like in your reply from $A$3 to Calculation!$A$2:$A$15 but this ends causing other problems like making the unique products shift down. I'm not getting the formula or reference logic.

Part 2

After customizing the combo boxes to my data set as mentioned above, I'm thining about how to make the users inputs stick on a row in a form in another tab of the file.

Using your examples, what if we wanted the user to be able to pick orderID 1010, Soundcard for $70 with 3 quantity. Then on the next row of a PO say the user also wanted to then pick OrderID 1010 Soundcard for $60 with a quantity of 10. All I can think of is a paste special values macro grabbing these cells the user wants to input row by row.

I'll be glad to submit what I'm working on if you are willing to look at it. Thanks.

Chris

Chris,

Part 1,

I´ll try to explain the formula in cell Calculation!B2:

The formula returns unique distinct products depending on what the selected value in cell Filter!$B$1. Example, OrderID 1010 is selected, Filter!$B$1 contains 1.

Step 1INDEX(Calculation!$A$2:$A$15, Filter!$B$1)

becomes

INDEX({1010, 1020, 1030, 1040, "", 0, 0, 0, 0, 0, 0, 0, 0, 0}, 1)

and returns 1010.

(order<>INDEX(Calculation!$A$2:$A$15, Filter!$B$1))

becomes

({1010, 1010, 1010, 1010, 1020, 1030, 1020, 1020, 1020, 1040}<>1010)

and returns

{FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

Step 2COUNTIF($B$1:B1, product)

becomes

COUNTIF("Unique products", {"Graphics card", "Soundcard", "Motherboard", "Soundcard", "Graphics card", "Keyboard", "Motherboard", "Graphics card", "Keyboard", "Keyboard"})

and returns

{0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

Step 3COUNTIF($B$1:B1;product)+(order<>INDEX(Calculation!$A$2:$A$15, Filter!$B$1))

becomes

{0, 0, 0, 0, 0, 0, 0, 0, 0, 0} + {FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

and returns

{0;0;0;0;1;1;1;1;1;1}

Step 4MATCH(0, COUNTIF($B$1:B1, product)+(order<>INDEX(Calculation!$A$2:$A$15, Filter!$B$1))

becomes

MATCH(0, {0;0;0;0;1;1;1;1;1;1})

and returns 1.

Step 5INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(order<>INDEX(Calculation!$A$2:$A$15, Filter!$B$1)), 0))

becomes

INDEX(product, 1)

and returns "Graphics card"

Contact form

Chris,

part 2,

Yes, you need a macro.

Take a look at this post: Basic data entry (vba)

Hi,

Great help!

But I am stuck at one place:- while adding the input range as 'uniqueorder' in the combo box properties, it is returning "Reference is not valid"

Any clue on what might be wrong?

Thanks again1

sharad,

There is something wrong with the named range 'uniqueorder'. What does your formula look like?

Hi Oscar!!!

I am trying to follow these steps with Excel 2003 and I am not managing to do it. In fact, the file I downloaded has "#NAME?" in the Calculation sheet and filters are not working. Could that be a problem of Excel version? If that is the case, would you be able to tell me what should I change in order to make it work?

Your help would be much mucgh appreciated!

Thanks

Patricia

Patricia,

The IFERROR function doesn´t work in excel 2003:

Try this function:

Thanks Oscar. This has been great, I do have one big problem however.

Hopefully you can address this.

I have three combo boxes in my file. Cell link to second combo box does not update all the time, resulting in blank value for third range. Is there a way to update the cell link consistently?

Thanks in advance.

Best,

Varun

Varun,

The question is why the cell link to second combo box does not update all the time?

My guess is something is wrong with the array formula or the dynamic named range.

I am having the same problem as Varun. I have only two combo boxes set up in sequence and the second one is not giving the right output in the cell that it is attached to based on what is chosen. The combo box shows the right selection, but its outputted value in its corresponding cell is wrong.

I have used the exact formulas that you have given in your example (thank you very much by the way), so I am not sure what is wrong.

What can I do to fix this?

Matt,

Send me an example workbook:

http://www.get-digital-help.com/excel-consulting/

Hi Oscar,

This is really great stuff! I was trying to create the dependent combo box since long but without any success. Thank you very much for your tutorial. Its really very helpful.

Can you please explain the formula in cell B7 in Filter tab (how it is displaying the values based on combo box selection) in detail - step by step manner please!!

Thanks

Keyur

Keyur,

Dynamic named rangesuniqueproduct - Calculation!B2:B4

uniqueorder - Calculation!A2:A3

order - Data!B3:B11

product - Data!A3:A11

Array formula in cell B7:Explaining array formula in cell B7:Step 1 - Identify "order" values equal to first combo box selection (cell B1)INDEX(uniqueorder, $B$1)=order)

becomes

INDEX({1010;1020}, 1)={1010;1010;1010;1010;1020;1020;1020;1020;1020})

becomes

1010={1010;1010;1010;1010;1020;1020;1020;1020;1020}

and returns

{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

Step 2 - Identify "product" values equal to second combo box selection (cell C1)(INDEX(uniqueproduct, $C$1)=product)

returns

{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

Step 3 - Return row numbersIF((INDEX(uniqueorder, $B$1)=order)*(INDEX(uniqueproduct, $C$1)=product), ROW(order)-MIN(ROW(order))+1, "")

becomes

IF({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}*{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

, {1;2;3;4;5;6;7;8;9}, "")

becomes

IF({0;1;0;1;0;0;0;0;0}

, {1;2;3;4;5;6;7;8;9}, "")

returns

{0;2;0;3;0;0;0;0;0}

Step 4 - Return the k-th smallest valueSMALL(IF((INDEX(uniqueorder, $B$1)=order)*(INDEX(uniqueproduct, $C$1)=product), ROW(order)-MIN(ROW(order))+1, ""), ROW(A1))

becomes

SMALL({0;2;0;3;0;0;0;0;0}, 1)

and returns 2.

Step 5 - Return a value of the cell at the intersection of a particular row and column, in a given rangeINDEX(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))

becomes

INDEX(Data!$A$3:$C$11, 2, 1)

becomes

INDEX({1010,"Graphics card",100;1010,"Soundcard",70;1010,"Motherboard",90;1010,"Soundcard",60;1020,"Graphics card",90;1020,"Keyboard",20;1020,"Motherboard",110;1020,"Graphics card",120;1020,"Keyboard",30}, 2, 1)

and returns

1010 in cell B7.

Thanks Oscar for your explanation. And sorry for such late reply.

I have another question.

I am using the dependent form comboboxes in my report. However, I have below problem.

I have two combo boxes - Year and Data. Data for both these comboboxes is as below -

YEAR DATA

2011 ACTUAL

2012 ACTUAL

2013 ACTUAL

2014 ACTUAL

2014 AOP

Idea is to show only unique Data value for selected year. The comboboxes are working fine without any problem. If I select year 2014 in Year combo, it shows "Actual" and "AOP" values in Data combo. and if I select 2013 in Year combo, it shows only "Actual" in the Data combo.

The problem is -

In the first step I select 2014 in year combo and select "AOP" in data combo.

Now after this when I select 2013 in year combo, data combo shows only "Actual". But it does not change the value in linked cell, which is the key to refresh my report data.

But if I select 2014 in year combo and "Actual" in data combo, and then again select 2013 in year combo, it works fine.

Don't know what is going wrong here.

Can you please help.

Thanks & Best Regards

Keyur

Keyur

Can you upload an example workbook?

Hi Oscar,

I really like the tool you made!! Do you also know a way to use this dependent filter trick for multiple independent order lines?

I want to use this technique on each order line to select the product I need.

Cheers,

Bram

Hi,

In your sample Excel file, 1010 and 1020 have the same number of unique entries --- 1010 has 3: Graphics Card, Soundcard, & Motherboard while 1020 also has 3: Graphics Card, Motherboard, and Keyboard.

If you delete the 2 entries of Keyboard in 1020, you only have 2 unique entries for 1020 and this is where it starts not to work. Now when you go to Filter worksheet tab and you start with 1010 and choose the 3rd entry which is Motherboard, the values are displayed. From here, however, if you change to 1020 and leave the 2nd combo box at Motherboard, there is no value returned even if 1020 has entry Motherboard; you have to refresh the combo box by choosing the first entry (Graphics Card) and then selecting Motherboard (all this time, you are at 1020) only at this time will the values for 1020 Motherboard be displayed.

How can this be resolved? I think the issue only shows when you have different number of entries in the first combo box (which was what happened when we deleted keyboard in 1020.

Anthony,

If you delete the 2 entries of Keyboard in 1020, you only have 2 unique entries for 1020 and this is where it starts not to work. Now when you go to Filter worksheet tab and you start with 1010 and choose the 3rd entry which is Motherboard, the values are displayed. From here, however, if you change to 1020 and leave the 2nd combo box at Motherboard, there is no value returned even if 1020 has entry MotherboardYou are right. If you select "1010" and "Motherboard", the second drop down list returns 3 in cell C1. Change it to 1020 and it still returns 3.

It would be possible to "reset" all selections if a new value is selected in the first drop down list with the use of a macro.

Assign this basic macro to the first drop down.

Sub FirstDrop()

Range("C1") = 1

End Sub

Download example file *.xlsm

combo-box-unique-distinct-dependent-lists2.xlsm

Hey Oscar i got a problem regarding your tutorial. I am using this tutorial in excel 2010. I have a problem at the array formula in cell A2. It gives me no values back. Even when i use ur example file and delete your formula in cell A2 and paste it in again it also gives me no values back. The cell just stays blank. Could you please help me?

Richard,

Can you provide your array formula in cell A2?

I've followed your example all the way to where you populate the filter sheet. I would like to know how to sort the data in four categories.

Hello Oscar,

This is a great tool, thank you for sharing. I have been trying to create a third filter for VAT (for example) but cannot work out how to extend your template. Would you be able to share an example with a third filter as I think from there I could work out how to add extra filters after that?

I would really appreciate your help with this. Thank you,

Patrick