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 (not Mac!) that let´s 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.

Example (Click to expand)

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 (Click to expand)

  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

Testimonials
The add-in makes dependent drop-down lists easy and flexible to implement in Excel. It fills a vital missing gap in Excel's functionality, because the drop-downs feed data into the underlying cells that they float over. It is simple and straightforward to integrate them with your own spreadsheets. Excellent support from the developer.

KS

 

Purchase Dependent Drop Down Lists Add-In for Excel 2007/2010 - Price $29 US

Add to Cart View Cart

 

 

Questions

Is there a money back guarantee?

Sure, you have un-conditional money back guarantee for 14 days.

Does it work on a Macintosh?

No

Why form controls?

A form control may have a macro assigned that runs when a new value is selected. This makes it possible to manipulate all drop down lists with a single macro.

Why a pivot table?

A pivot table filters and sorts values extremly quickly. Of course, it is possible to filter and sort values using vba but working with large data sets, a pivot table rules when it comes to speed!

Do the excel table, pivot table and the drop down lists have to be on the same sheet?

No, but they must be in the same workbook.

What customizations can I do with a combobox (form control)?

You can change the number of drop down lines, 3d effects and link each drop down list to a single cell.

Can I hide the pivot table sheet?

Yes! Right click on the sheet and click on "Hide"

Can I change the order of drop down lists?

Yes, rearrange the table columns and then use the AddIn to create drop down lists.

Purchase Dependent Drop Down Lists Add-In for Excel 2007/2010 - Price $29 US Add to Cart View Cart

How the Purchase Process Works?

  • Payment is accepted via PayPal.
  • After you finish payment, you are redirected to the download page. You will also receive an email with the download link.
  • You have five attempts to download the file.
  • The download link will expire in 120 hours (5 days).

If you can´t downloading the file, contact me.

 

Related posts:

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

Invoice template with dependent drop down lists in excel

Dependent drop down lists – Enable/Disable selection filter

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

Use a drop down list to extract and concatenate unique distinct values