Josh asks:

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

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

Create a unique distinct list from column A, Sheet1

1. Select Sheet2
2. Select cell A3. (Cell A2 is empty)
3. Type =INDEX(order,MATCH(0,COUNTIF($A$1:B2,order),0)) + CTRL + SHIFT + ENTER. This is an array formula!!
4. Copy cell A3 and paste it down as far as needed.

Sheet 2

Create a dynamic named range to get unique distinct list

1. Select Sheet2
2. Click "Formulas" tab
3. Click "Name Manager"
4. Click "New..."
5. Type a name. I named it "uniqueorder".
6. Type =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$3:$A$1000="", "", 1))+1, 1) in  "Refers to:" field.
7. Click "Close" button

Create drop down list

1. Select Sheet1
2. Select cell E2
3. Click Data tab
4. Click Data validation button
5. Click "Data validation..."
6. Select List in the "Allow:" window.
7. Type =uniqueorder in the "Source:" window
8. Click OK!

Setup and create second dependent drop down list

Create a dynamic named range

1. Select sheet1
2. Click "Formulas" tab
3. Click "Name Manager"
4. Click "New..."
5. Type a name. I named it "product". (See attached file at the end of this post)
6. Type =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) in  "Refers to:" field.
7. Click "Close" button

Create a unique distinct list from column B, sheet1.

1. Select Sheet2
2. Select cell B3. (Cell B2 is empty.)
3. 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!!
4. Copy cell B3 and paste it down as far as needed.

Sheet2

Create a dynamic named range to get unique distinct list

1. Select Sheet2
2. Click "Formulas" tab
3. Click "Name Manager"
4. Click "New..."
5. Type a name. I named it "uniqueproduct".
6. Type =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$3:$B$1000="", "", 1))+1, 1) in  "Refers to:" field.
7. Click "Close" button

Create drop down list

1. Select Sheet1
2. Select cell E5
3. Click Data tab
4. Click Data validation button
5. Click "Data validation..."
6. Select List in the "Allow:" window.
7. Type =uniqueproduct in the "Source:" window
8. Click OK!

Setup and create third dependent drop down list

Create a dynamic named range

1. Select sheet1
2. Click "Formulas" tab
3. Click "Name Manager"
4. Click "New..."
5. Type a name. I named it "continent".
6. Type =OFFSET(Sheet1!$C$2, 0, 0, COUNTA(Sheet1!$C$2:$C$1000))in  "Refers to:" field.
7. Click "Close" button

Create a unique distinct list from column C, Sheet1

1. Select Sheet2
2. Select cell C3. (Cell C2 is empty)
3. 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!!
4. Copy cell C3 and paste it down as far as needed.

Sheet 2

Create a dynamic named range to get unique distinct list

1. Select Sheet2
2. Click "Formulas" tab
3. Click "Name Manager"
4. Click "New..."
5. Type a name. I named it "uniquecontinent".
6. Type =OFFSET(Sheet2!$C$2, 0, 0, COUNT(IF(Sheet2!$C$2:$C$1000="", "", 1)), 1) in  "Refers to:" field.
7. Click "Close" button

Create drop down list

1. Select Sheet1
2. Select cell E8
3. Click Data tab
4. Click Data validation button
5. Click "Data validation..."
6. Select List in the "Allow:" window.
7. Type =uniquecontinent in the "Source:" window
8. Click OK!

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

Download example workbook

dependent drop down lists - selection filter.xls
(Excel 97-2003 Workbook *.xls)

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

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

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

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

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

Related posts:

Create dependent drop down lists containing unique distinct values in excel

Invoice template with dependent drop down lists in excel

Create dependent drop down lists containing unique distinct values in multiple rows

Create a drop down calendar in excel

Create a drop down list containing alphabetically sorted values in excel