Here is a list of order numbers and products.

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.

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!

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)

 

Related article:
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

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

Dependent Drop Down Lists AddIn

Dependent drop down lists is an AddIn for Excel 2007/2010/2013 (not Mac!) that lets you easily create drop down lists (comboboxes, form controls) in Microsoft® Excel.

What are dependent drop down lists?

A dependent drop down list changes it´s values automatically depending on selected values in previous drop down lists on the same row.

The first drop down list in the picture above contains values from "Region" column. The second drop down list contains values from "Country" column and the third from City column.

Now depending on the selected value in the first drop down list, the second and third drop down lists change their values automatically.

In the picture above, the first drop down list has the value "Europe" selected, the second "France and in the third you can choose between "Lyon" or "Paris". You can see how the columns are related to each other if you examine the table above.

Features

  • Utilizes a pivot table to quickly filter and sort values for maximum speed
  • The drop down lists are populated using Visual Basic for Applications
  • No excel formulas
  • Easily copy values: Each drop down list is linked to the cell behind.
  • You can create as many drop down lists you want
  • You can send workbooks containing dependent drop down lists to  friends, colleagues etc, as long as they can open macro-enabled workbooks.

For simplicity, your data set must be an excel table. That is easily created if you don´t know how. A vba macro is required in your workbook. The AddIn shows you how in a few simple steps.

 

How to use the AddIn 

  1. Go to tab "Add-Ins"
  2. Click "Dependent Drop Down Lists AddIn" button
  3. Select a table


  4. Select desired table column headers
  5. Select a cell range where you want your drop down lists

  6. Click "Next"
  7. Copy code to a module
  8. Click Close