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
Related posts:
Excel vba: Populate a combo box (form control)
Dependent drop down lists – Enable/Disable selection filter
Working with combo boxes (Form Control) using vba
Create dependent drop down lists containing unique distinct values in multiple rows
Recommended categories
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
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 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 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 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.
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