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 downloading the attached excel file before reading this post.

Setup and create first drop down list

Create a dynamic named range

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(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000)) in "Refers to:" field.
Click "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
Click "Formulas" tab
Click "Name Manager"
Click "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.
Click "Close" button

Create drop down list

Select Sheet1
Select cell E2
Click Data tab
Click Data validation button
Click "Data validation..."
Select List in the "Allow:" window.
Type =uniqueorder in the "Source:" window
Click OK!

Setup and create second dependent drop down list

Create a dynamic named range

Select sheet1
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(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) in "Refers to:" field.
Click "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
Click "Formulas" tab
Click "Name Manager"
Click "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.
Click "Close" button

Create drop down list

Select Sheet1
Select cell E5
Click Data tab
Click Data validation button
Click "Data validation..."
Select List in the "Allow:" window.
Type =uniqueproduct in the "Source:" window
Click OK!

Setup and create third dependent drop down list

Create a dynamic named range

Select sheet1
Click "Formulas" tab
Click "Name Manager"
Click "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.
Click "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
Click "Formulas" tab
Click "Name Manager"
Click "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.
Click "Close" button

Create drop down list

Select Sheet1
Select cell E8
Click Data tab
Click Data validation button
Click "Data validation..."
Select List in the "Allow:" window.
Type =uniquecontinent in the "Source:" window
Click OK!

Example picture sheet1, first drop down list is blank and second drop down list contains all unique values.

Download Excel file

dependent-drop-down-lists-selection-filter.xls

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 […]

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. […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

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 […]

Invoice template with dependent drop down lists

This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]

Comments (8)

8 Responses to “Dependent drop down lists – Enable/Disable selection filter”

Josh says:

April 18, 2011 at 2:53 pm

Outstanding, this works beautifully. Thanks for sharing your expertise.

Reply
Charlie says:

August 10, 2011 at 8:07 am

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!

Reply
Oscar says:

August 11, 2011 at 9:58 am

Charlie,

Question 1:
Download 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

Reply
charlie says:

August 12, 2011 at 1:51 pm

Thanks very much!!!

Reply
Fabiola says:

October 2, 2011 at 2:00 am

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?

Reply
Fabiola says:

October 2, 2011 at 3:46 am

Can a fourth and five column be added?

Reply
Nilhan says:

February 1, 2012 at 3:49 pm

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 to be downloaded for free somewhere?

Thanks a lot for answering my question.

Nilhan

Reply
Diane says:

August 24, 2014 at 6:47 am

Thanks for finally writing about > Dependent drop down lists - Enable/Disable selection filter | Get
Digital Help - Microsoft Excel resource < Loved it!

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.

Contact Oscar

You can contact me through this contact form