Dependent drop down lists – Enable/Disable selection filter
Author: Oscar Cronquist Article last updated on January 16, 2019
I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know of a way to see additional information in drop down #4 if there are no selections in drop down #2 and #3.
Currently, I have hemisphere in drop down 1, Sector in drop down 2, Region in drop down 3, and Area is drop down 4.
If I select a value in drop down #1 (Hemisphere), I would like to see what areas I can filter on in drop down #4 without having to select values in drop down 2 and 3.
Is there a way to make that work?
Answer:
Yes, I have simplified my answer to three drop down lists. I recommend geting the attached excel file before reading this post.
Setup and create first drop down list
Create a dynamic named range
- 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(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000)) in "Refers to:" field.
- Press with left mouse button on "Close" button
Create a unique distinct list from column A, Sheet1
- Select Sheet2
- Select cell A3. (Cell A2 is empty)
- Type =INDEX(order,MATCH(0,COUNTIF($A$1:B2,order),0)) + CTRL + SHIFT + ENTER. This is an array formula!!
- Copy cell A3 and paste it down as far as needed.
Sheet 2
Create a dynamic named range to get unique distinct list
- Select Sheet2
- 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".
- Type =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$3:$A$1000="", "", 1))+1, 1) in "Refers to:" field.
- Press with left mouse button on "Close" button
Create drop down list
- Select Sheet1
- Select cell E2
- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window.
- Type =uniqueorder in the "Source:" window
- Press with left mouse button on OK!
Setup and create second dependent drop down list
Create a dynamic named range
- Select sheet1
- 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(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) in "Refers to:" field.
- Press with left mouse button on "Close" button
Create a unique distinct list from column B, sheet1.
- Select Sheet2
- Select cell B3. (Cell B2 is empty.)
- Type =INDEX(product, MATCH(0, COUNTIF($B$1:B2, product)+IF(Sheet1!$E$2<>0, order<>Sheet1!$E$2, order=""), 0))Â + CTRL + SHIFT + ENTER. This is an array formula!!
- Copy cell B3 and paste it down as far as needed.
Sheet2
Create a dynamic named range to get unique distinct list
- Select Sheet2
- 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".
- Type =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$3:$B$1000="", "", 1))+1, 1) in "Refers to:" field.
- Press with left mouse button on "Close" button
Create drop down list
- Select Sheet1
- Select cell E5
- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window.
- Type =uniqueproduct in the "Source:" window
- Press with left mouse button on OK!
Setup and create third dependent drop down list
Create a dynamic named range
- Select sheet1
- 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 "continent".
- Type =OFFSET(Sheet1!$C$2, 0, 0, COUNTA(Sheet1!$C$2:$C$1000))in "Refers to:" field.
- Press with left mouse button on "Close" button
Create a unique distinct list from column C, Sheet1
- Select Sheet2
- Select cell C3. (Cell C2 is empty)
- Type =INDEX(continent, MATCH(0, COUNTIF($C$1:C1, continent)+(Sheet1!$E$2<>0)*(order<>Sheet1!$E$2)+(Sheet1!$E$5<>0)*(product<>Sheet1!$E$5), 0))Â + CTRL + SHIFT + ENTER. This is an array formula!!
- Copy cell C3 and paste it down as far as needed.
Sheet 2
Create a dynamic named range to get unique distinct list
- Select Sheet2
- 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 "uniquecontinent".
- Type =OFFSET(Sheet2!$C$2, 0, 0, COUNT(IF(Sheet2!$C$2:$C$1000="", "", 1)), 1) in "Refers to:" field.
- Press with left mouse button on "Close" button
Create drop down list
- Select Sheet1
- Select cell E8
- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window.
- Type =uniquecontinent in the "Source:" window
- Press with left mouse button on OK!
Example picture sheet1, first drop down list is blank and second drop down list contains all unique values.
Dependent drop down lists category
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Excel categories
8 Responses to “Dependent drop down lists – Enable/Disable selection 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.
Outstanding, this works beautifully. Thanks for sharing your expertise.
Hello,
Great work so far but I was wondering if it could do something else...?
If I start off with all the down lists as empty and then, say, select keyboard as the product it successfully shows only US and Europe as the options - but shows both order ID, when it can only come from 1020.
Is it possible for the list to work both ways? Also, what if you were to select Asia and Graphics card - could it automatically fill in 1020 (the auto filling is more of a nice feature than necessary..:))
Any help or advice would be greatly appreciated, thanks!
Charlie,
Question 1:
Get the example file
dependent-drop-down-lists-selection-filter_charlie.xls
Question 2:
I think autofilling is possible with combo boxes:
Apply dependent combo box selections to a filter in excel 2007
Thanks very much!!!
How can additional colums with more criteria be added to the exsisting three columns? Can drop boxes be added to multiple rows, such as in an Invoice? I seen your example "Create dependent drop down lists containing unique distict values in multiple rows" but how can those formulas be combined with the formulas in this example?
Can a fourth and five column be added?
Hi Oscar,
Thanks for the great work you are doing.
I am not an expert, and I was wondering if there is any way that you can do and publish the same for 7 columns.
Also, is the excel addin that you have created available for free somewhere?
Thanks a lot for answering my question.
Nilhan
Thanks for finally writing about > Dependent drop down lists - Enable/Disable selection filter | Get
Digital Help - Microsoft Excel resource < Loved it!