get-digital-help.com

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.