Apply dependent combo box selections to a filter
now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set.
Answer:
Table of contents
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
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"
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "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.
- Press with left mouse button on "Close" button
Named range "product"
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "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.
- Press with left mouse button on "Close" button
3. 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.
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"
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "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.
- Press with left mouse button on "Close" button
Named range "uniqueproduct"
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "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.
- Press with left mouse button on "Close" button
5. Create Combo boxes
- Press with left mouse button on "Developer" tab on the ribbon
- Press with left mouse button on "Insert" button
- Press with left mouse button on Combo box button
- Place combo box on cell B2 on sheet "Filter
Create a second combo box on cell C2.
5.1 Setup Combo box 1
- Select sheet "Filter"
- Press with right mouse button on on combo box on cell B2
- Press with left mouse button on "Format Control..."
- Press with left mouse button on tab "Control"
- Type uniqueorder in Input range:
- Select cell B1 in Cell link:
- Press with left mouse button on OK
Setup Combo box 2
- Select sheet "Filter"
- Press with right mouse button on on combo box on cell C2
- Press with left mouse button on "Format Control..."
- Press with left mouse button on tab "Control"
- Type uniqueproduct in Input range:
- Select cell C1 in Cell link:
- Press with left mouse button on OK
I have hidden the values in cell B1 and C1.
- Select B1:C1
- Press Ctrl + 1
- Press with left mouse button on "Custom" in Category window
- Type ,,, in the "Type:" field
- Press with left mouse button on OK
6. 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.
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
Combobox category
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Dependent drop down lists category
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
Excel categories
43 Responses to “Apply dependent combo box selections to a filter”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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.
Get 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
https://198.74.52.105/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 ;;;
https://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,
Get the 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 1
INDEX(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 2
COUNTIF($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 3
COUNTIF($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 4
MATCH(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 5
INDEX(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 got 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:
https://198.74.52.105/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 ranges
uniqueproduct - 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 numbers
IF((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 value
SMALL(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 range
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))
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 Motherboard
You 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
Get the Excel 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
Thank you very much.
Nice examples and a really great tool.
Keep it up.
JUST - THANK YOU!
I need this idea but by using an userform with combobox vba to collect the data from my excel sheet.
Thanks for the example.
How would you go about adding a third combo box, e.g. make ?
Thanks so much! Would it be possible to add an "All" option in the filters?