Create dependent drop down lists containing unique distinct values in excel

Create dependent drop down lists containing unique distinct values in excel

Here is a list of order numbers and products.

dependent-drop-down-list

We are going to create two drop down lists.

The first drop down list contains unique distinct values from column A.

The second drop down list contains unique distinct values from column B, based on chosen value in the 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

  1. Select Sheet2
  2. Select cell A2
  3. Type “=INDEX(order,MATCH(0,COUNTIF($A$1:A1,order),0))” + CTRL + SHIFT + ENTER
  4. Copy cell A2 and paste it down as far as needed.

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”. (See attached file at the end of this post)
  6. Type =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$2:$A$1000=””, “”, 1)), 1) in  “Refers to:” field.
  7. Click “Close” button

Create drop down list

  1. Select Sheet1
  2. Select cell D2
  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!

Here is a picture of what we have accomplished so far.

dependent-drop-down-list1

How to create a secondary unique list based on only one chosen cell value in 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 “product”. (See attached file at the end of this post)
  5. Type =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) in  “Refers to:” field.
  6. Click “Close” button

Create a unique distinct list from column B

  1. Select Sheet2
  2. Select cell B2
  3. Type “=INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(order<>Sheet1!$D$2), 0))” + CTRL + SHIFT + ENTER
  4. Copy cell B2 and paste it down as far as needed.

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”. (See attached file at the end of this post)
  6. Type =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$2:$B$1000=””, “”, 1)), 1) in  “Refers to:” field.
  7. Click “Close” button

Create drop down list

  1. Select Sheet1
  2. Select cell D5
  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!

dependent-drop-down-list2

Download example workbook

unique-distinct-dependent-lists.xls (Excel 97-2003 Workbook *.xls)

Download example workbook with a third column of data

unique-distinct-dependent-lists1-three-columns.xls (Excel 97-2003 Workbook *.xls)


Courtesy: get digital help

 

Show Comments

No Responses Yet

Leave a Reply