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 the chosen value in the first drop-down list.

Watch a video on how to set up dependent drop-down lists

Create a dynamic named range

A named range is great for lists that expand, however I recommend an Excel defined table if you have Excel 2007 or a later version.

  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

Recommended article

Create a dynamic named range in excel

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns […]

Comments(12) Filed in category: Excel, Named range

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.

Recommended article:

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

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

Recommended article:

Explaining OFFSET function

In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]

Comments(2) Filed in category: Excel

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

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.

Recommended article

Add new items to a drop down list automatically

A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you […]

Comments(9) Filed in category: Drop down lists, Excel

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)

Recommended articles:

Create a drop down list containing only unique distinct alphabetically sorted text values

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Comments(76) Filed in category: Drop down lists, Excel

Create dependent drop down lists containing unique distinct values

Here is a list of order numbers and products. We are going to create two drop-down lists. The first drop […]

Comments(113) Filed in category: Dependent drop down lists, Excel

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

Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]

Comments(12) Filed in category: Dependent drop down lists, Excel

Invoice template with dependent drop down lists

Overview This post descibes how to use a basic invoice template I created. The invoice template let´s you use dropdown […]

Comments(11) Filed in category: Dependent drop down lists, Excel, Invoice, Templates

Dependent drop down lists – Enable/Disable selection filter

Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]

Comments(8) Filed in category: Dependent drop down lists, Excel

Dependent data validation lists in multiple rows

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent […]

Comments(29) Filed in category: Dependent drop down lists, Excel

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.

Watch a video where I demonstrate the Add-In

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

[/expand]

 

Purchase Dependent Drop Down Lists Add-In for Excel 2007/2010/2013 - Price $19 USD

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.

Can I create dropdown lists on multiple rows?

Yes, insert dropdown lists on multiple rows using the add-in. However, you can´t copy and paste the dropdown lists yourself, they have unique names.

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.

If I construct a UI using the AddIn, then will anyone be able to use those on their own computers without adding the AddIn themselves?
Yes, they will be able to use the drop down lists.

If so, then would they just need to enable macros?
Yes, that is correct.

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

Isn’t it surprising that making dependent drop-down lists is still so difficult in Excel? The Dependent Drop Down Lists add-in makes it much simpler. Plus, they have excellent customer support if you get stuck.

Benjamin Charlton, Dubai

 

Purchase Dependent Drop Down Lists Add-In for Excel 2007/2010/2013 - Price $19 USD
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.