Author: Oscar Cronquist Article last updated on January 16, 2019

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

Setup and create first drop down list

Create a dynamic named range

  1. Press with left mouse button on "Formulas" tab
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "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. Press with left mouse button on "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. Press with left mouse button on "Formulas" tab
  3. Press with left mouse button on "Name Manager"
  4. Press with left mouse button on "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. Press with left mouse button on "Close" button

Create drop down list

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

Setup and create second dependent drop down list

Create a dynamic named range

  1. Select sheet1
  2. Press with left mouse button on "Formulas" tab
  3. Press with left mouse button on "Name Manager"
  4. Press with left mouse button on "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. Press with left mouse button on "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. Press with left mouse button on "Formulas" tab
  3. Press with left mouse button on "Name Manager"
  4. Press with left mouse button on "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. Press with left mouse button on "Close" button

Create drop down list

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

Setup and create third dependent drop down list

Create a dynamic named range

  1. Select sheet1
  2. Press with left mouse button on "Formulas" tab
  3. Press with left mouse button on "Name Manager"
  4. Press with left mouse button on "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. Press with left mouse button on "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. Press with left mouse button on "Formulas" tab
  3. Press with left mouse button on "Name Manager"
  4. Press with left mouse button on "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. Press with left mouse button on "Close" button

Create drop down list

  1. Select Sheet1
  2. Select cell E8
  3. Press with left mouse button on Data tab
  4. Press with left mouse button on Data validation button
  5. Press with left mouse button on "Data validation..."
  6. Select List in the "Allow:" window.
  7. Type =uniquecontinent in the "Source:" window
  8. 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.