# Create dependent drop down lists containing unique distinct values

### Table of Contents

## 1. Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists.

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.

- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "New..."
- Type a name. I named it "order". (See attached file at the end of this post)
- Type =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000)) inÂ "Refers to:" field.
- Press with left mouse button on "Close" button

Recommended article

Recommended articles

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

**Create a unique distinct list from column A**

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

Recommended article:

Recommended articles

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

**Create a dynamic named range to get unique distinct list**

- Select Sheet2
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "New..."
- Type a name. I named it "uniqueorder". (See attached file at the end of this post)
- Type =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$2:$A$1000="", "", 1)), 1) inÂ "Refers to:" field.
- Press with left mouse button on "Close" button

Recommended article:

Recommended articles

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

Recommended articles

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

**Create drop down list**

- Select Sheet1
- Select cell D2
- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window.
- Type =uniqueorder in the "Source:" window
- Press with left mouse button on OK!

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

Recommended article

Recommended articles

A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]

### How to create a secondary unique list based on only one chosen cell value in first drop down list

**Create a dynamic named range**

- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "New..."
- Type a name. I named it "product". (See attached file at the end of this post)
- Type =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) inÂ "Refers to:" field.
- Press with left mouse button on "Close" button

**Create a unique distinct list from column B**

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

**Create a dynamic named range to get unique distinct list**

- Select Sheet2
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "New..."
- Type a name. I named it "uniqueproduct". (See attached file at the end of this post)
- Type =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$2:$B$1000="", "", 1)), 1) inÂ "Refers to:" field.
- Press with left mouse button on "Close" button

**Create drop down list**

- Select Sheet1
- Select cell D5
- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window.
- Type =uniqueproduct in the "Source:" window
- Press with left mouse button on OK!

**Get example workbook**

unique distinct dependent lists.xls

(Excel 97-2003 Workbook *.xls)

**Get example workbook with a third column of data**

unique-distinct-dependent-lists1 three columns.xls

(Excel 97-2003 Workbook *.xls)

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

How can i use these list for multiple rows?

I would like to use these lists for multiple rows and let people enter detailed records.

Answer:

In my example workbook, I have created three sheets:

- Multiple rows
- Data
- Calculation

The selected drop-down list gets all values from a single column on sheet "Calculation".

The array formula on sheet "Calculation" has to "know" which cell you have selectedÂ and the adjacent cell value on sheet "Multiple rows".

### Setup an automatic event on sheet "Multiple rows" (VBA)

- Press Alt-F11 to open VB editor
- Double press with left mouse button on Sheet1 "Multiple rows" in project window.
- Copy and paste vba code below into code window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Sheets("Calculation").Range("D1") = Sheets("Multiple rows").Range("A" & ActiveCell.Row) End Sub

Cell D1 on sheet "Calculation" is updated instantly whenever you select a cell on sheet "Multiple rows"

### Setting up the "Calculation" sheet

Array formula in cell B2:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell B2 and paste it down as far as needed. You can read how this formula works here: 5 easy ways to extract unique distinct values

## 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Â **

- Go to tab "Add-Ins"
- Press with left mouse button on "Dependent Drop Down Lists AddIn" button

- Select a table

- Select desired table column headers

- Select a cell range where you want your drop down lists

- Press with left mouse button on "Next"
- Copy code to a module

- Press with left mouse button on Close

[/expand]

## 3. Create a drop down list containing alphabetically sorted values

This section describes how to create a drop-down list populated with sorted values from A to Z. The sorted list is dynamic and it adds new values as you type them. The picture below shows a list containing some random values.

First we need to build a cell reference that expands when we add values to the list, named ranges allow us to do that.

However, if you own Excel 2007 or a later version I highly recommend using an Excel defined table instead.

It has a built-in dynamic cell reference system, named structured referencing.

### 3.1 Create a dynamic named range "List"

The following instructions tells you how to build an automatically expanding cell range.

- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "New..."
- Type a name. I named it "List". (See attached file at the end of this post)
- Type
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001))
inÂ "Refers to:" field.

- Press with left mouse button on "Close" button

Want to learn more about named ranges? Read this:

Recommended articles

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

### 3.2 Create a dynamic list sorted from A to Z

- Select Sheet2
- Select cell A1
- Type
=IF(COUNTA(List)>=ROWS($A$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "")
**+ CTRL + SHIFT + ENTER** - Copy cell A2 and paste it down as far as needed.

This article explains how to extract a unique distinct list sorted alphabetically:

Recommended articles

Table of Contents Extract a unique distinct list sorted from A to Z Unique distinct list sorted alphabetically based on […]

You can even use a condition if you prefer, read more here:

Recommended articles

This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]

### 3.3 Create a dynamic named range "SortedValues"

- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "New..."
- Type a name. I named it "SortedValues". (See attached file at the end of this post)
- Type
=OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A$1:$A$1001))
inÂ "Refers to:" field.

- Press with left mouse button on "Close" button

Learn more about the function OFFSET function:

Recommended articles

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

### 3.4 How to create a drop down list containing dynamic values

- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window. See picture below.
- Type
=SortedValues
in the "Source:" window

- Press with left mouse button on OK!

If you are using an excel defined table, read this:

Recommended articles

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

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

### 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! Press with right mouse button on on the sheet and press with left mouse button 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.*

*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.*

### How the Purchase Process Works?

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

If you canÂ´t geting the file, contact me.

### Dependent drop down lists category

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

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

### Excel categories

### 145 Responses to “Create dependent drop down lists containing unique distinct values”

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

finally! :)

but attached file is empty?

david,

A new excel file is attached!

Thanks for commenting!

works great and marvellous work!

thansk! ;)

this process works to perfection! thanks so much for posting. now if i only knew how to apply these dropdown selections to a filter, i'd be set.

Josh,

read this post: Apply dependent combo box selections to a filter in excel 2007

I just came across your site as I was looking to do this very thing, (with a slight twist). I am setting up a sheet where the same type of dynamic lists were needed for multiple entries. I.e. I needed to be able to make an entry in one column from a drop down list and have a dependant list on the same line in the next column, THEN have the dependant list update based on the new entry on the next line. Using you list formulas and adding a very slight modification I am able to implement the dynamic drops lists I needed. Thanks very much!

how would i make this also be alphabetical?

Fantastic!

jon,

Sheet2:

Array formula in cell A2:

=INDEX(order, MATCH(SMALL(IF(COUNTIF($A$1:A1, order)=0, COUNTIF(order, "<"&order), ""), 1), IF(COUNTIF($A$1:A1, order)=0, COUNTIF(order, "<"&order), ""), 0)) + CTRL + SHIFT + ENTER. Copy cell A2 and paste it down. Array formula in cell B2: =INDEX(product, MATCH(SMALL(IF((COUNTIF($C$1:C1, product)+(order<>Sheet1!$D$2))=0, COUNTIF(product, "<"&product), ""), 1), IF((COUNTIF($C$1:C1, product)+(order<>Sheet1!$D$2))=0, COUNTIF(product, "<"&product), ""), 0)) + CTRL + SHIFT + ENTER. Copy cell B2 and paste it down as far as needed.

Hi,

I tried to use this formula, but the unique values some how are giving duplicate values for me.

Sharmila,

Did you change the relative and absolute cell references (bolded) in both formulas?

=INDEX(order,MATCH(0,COUNTIF(

$A$1:A1,order),0))=INDEX(product, MATCH(0, COUNTIF(

$B$1:B1, product)+(order<>Sheet1!$D$2), 0))Oscar,

thanks for the reply. i entered the formula into A2 and that worked but the formula for B2 didnt work. i was wondering maybe it was becasue the formula referenced "$c$1:c1" so i changed it to reference "$b$1:b1" since i was entering the formula into B2 but that didnt work either. any advice?

thanks in advance.

figured it out. it had to do with the $C$1:C1 as i guessed as well as all the spaces in the formula. i took out the spaces and it worked perfect.

thanks! great formula!

I am happy you figured it out!

Hi,

I have a question, how can i use these list for multiple rows?

I would like to use these lists for multiple rows and let people enter detailed records.

I have no words to thank you

The answer to Sharmilas question can be found here: Create dependent drop down lists containing unique distinct values in multiple rows

Hi,

Would there be any changes in the formula if Product in the first column and Order#ID in the second column?

Thanks

sanlen

sanlen,

No, the formulas work with both numbers or text.

Hey, thanks for this wonderful information.

I need one more addition. How can i make it generic, instead of copying the cell & pasting it all the way down?

Suraj,

I am not sure I understand.

Try this formula in A2 and cells down:

=INDEX(order, SMALL(IF(MATCH(order, order, 0)=(ROW(order)-MIN(ROW(order))+1), ROW(order)-MIN(ROW(order))+1), ROW()-1)) + CTRL + SHIFT + ENTER

Array formula in B2 and cells down:

=INDEX(product, SMALL(IF(IF(ISERROR(MATCH(IF(order=Sheet1!$D$2, product, FALSE), IF(order=Sheet1!$D$2, product, ""), 0)), "", MATCH(IF(order=Sheet1!$D$2, product, FALSE), IF(order=Sheet1!$D$2, product, ""), 0))=(ROW(product)-MIN(ROW(product))+1), ROW(product)-MIN(ROW(product))+1), ROW()-1)) + CTRL + SHIFT + ENTER

Oscar,

I am trying to do this on multiple columns?

Crop Select SF/ST Qty

ARA PLANTS UNTREATED 1 PLANT

ARA SEEDS STANDARD TREATMENT 1 SDS

ARA SEEDS UNTREATED 10 SDS

ARA 100 SDS

ASA 1 SDS

ASA 10 SDS

ASA 50 SDS

BDB SEEDS ITALIAN TREATMENT 20 KG

BDB SEEDS LORSB./THIR/APR. 25 KG

BDB SEEDS STANDARD TREATMENT 25 SDS

BDB SEEDS UNTREATED 5 KG

BDB 5 SDS

BNC SEEDS ITALIAN TREATMENT 0,100 KG

BNC SEEDS LORSB./THIR/APR. 0,250 KG

BNC SEEDS STANDARD TREATMEN 1 KG

BNC SEEDS UNTREATED 1 SDS

BNC 250 SDS

The column 2 and 3 are independant of each other but dependent on the first column

Deb,

I think you need to read this article: https://www.get-digital-help.com/2010/11/16/create-dependent-drop-down-lists-containing-unique-distinct-values-in-multiple-rows/

You can then apply dependent drop down lists on multiple rows.

Oscar,

I meant to say:

Instead of copying cell A2 & B2 all the way down in Sheet2, is there a generic way of doing that? Can the formula be automated, so as to avoid #N/A in the cells on Sheet2?

Suraj,

#N/AÂ´s are avoided by using a dynamic named range and are not displayed in any of the drop down lists.

=OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$2:$A$1000="", "", 1)), 1).

IFERROR() function prevents error messages in cells.

Oscar,

Thanks a lot. I would rather hide the calculation sheet(Sheet2), than to try and hide/manipulate the #N/A values. :-)

Anyways, Great Work. Keep it up!

It helped me a lot.

Hi,

I am trying to filter values in a drop down menu based on the values entered in the previous drop down menu.

eg: i have values 1,2,3,4,etc in drop down cell 1.

i want to filter values in drop down cell which contains 111,222,22,333,444,etc..

If I enter 2 in cell 1 then the second cell should filter values and show only 222 and 22...

Thanks in advance...

Amit

Amit,

Instead of creating unique distinct values you need to filter values.

try formula:

=INDEX(array, SMALL(IF(ISERROR(SEARCH($H$3, array)), "", ROW(array)-MIN(ROW(array))+1), ROW(A1))) + CTRL + SHIFT + ENTER

Adjust cell references and named ranges to your sheet.

Its working very well...........but i want it like this

press with left mouse button on an alphabet its should move to the corresponding list

Its working very well...........but i want it like this

on press with left mouse button on an alphabet its should move to the corresponding list

if any alphabet is entered it should get all list corresponding to that alphabet

Shikhar,

can you elaborate? Can you provide an example?

For Eg:-i have created a sheet named list

it has 5 columns

1st column contains:4 project types development,documentation,maintainence,lotus notes

2nd column contains:all the development projects

3rd column contains:-all the documentation projects

4th column contains:-maintainence projects

5th column contains:-lotus notes projects

I have put a validation on some other sheets so that according to the project types the projects are coming and the user can select his particular choice.

Till here its working fine

But if a user is pressing any alphabet say "A" I want is that the control should go to the project starting with A........

@ oscar thanks for replying me and if possible get some alternative for this......

shikhar,

Predictive text is possible in combo boxes (ActiveX) with some programming.

See Contextures: Excel Data Validation Combo box using Named Ranges

Hi Oscar,

Thank you for the wonderful solution.

I am attempting to recreate what you have done in Excel 2007. I seem to be having issues with the following line:

=INDEX(order,MATCH(0,COUNTIF($A$1:A1,order),0)) + CTRL + SHIFT + ENTER

If I open your file in compatibility mode then the following formula is found in its place

{=INDEX(order,MATCH(0,COUNTIF($A$1:A1,order),0)) }

but if I attempt to select the formula then the brackets automatically are removed and I get an error message in the cell.

Any insigh is greatly appreciated.

Thank you in advance for your assistance.

Roy

Roy Shore,

The brackets indicate array formula. Press and hold CTRL + SHIFT and then press Enter to create an array formula.

Read this: Introduction to Array Formulas

Thanks for commenting!

Oscar........ thanks a lot for the solution this is Exactly what i wanted.............

I currently am using a Mac with Excel 2008 and can't seem to locate the "Formulas tab" and "Name Manager" to create a dynamic named range like you did. Can you help me? Just learning...

Darold,

I wish I could help you! But I donÂ´t know.

Found it! It is under the "Insert" tab, then "Name", then "Define".

Thanks!

What is the formula if there is a third column of data?

Patrick,

Get the example workbook with a third column of data

unique-distinct-dependent-lists1 three columns.xls

(Excel 97-2003 Workbook *.xls)

Oscar, thank you for this information. This post is a great help. I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know of a way to see additional information in drop down #4 if there are no selections in drop down #2 and #3. Currently, I have hemisphere in drop down 1, Sector in drop down 2, Region in drop down 3, and Area is drop down 4. If I select a value in drop down #1 (Hemisphere), I would like to see what areas I can filter on in drop down #4 without having to select values in drop down 2 and 3. Is there a way to make that work?

Josh,

Great question!

Read this: Dependent drop down lists â€“ Enable/Disable selection filter

Oscar, This post satisfied an exact requirement of mine and has been very helpful indeed. However, if I select my way thru the drop down lists too quickly in Excel 2010 it causes it to crash. If I wait for the field to populate each time I make a selection it works fine but if I hurry thru it then it always crashes. Tried the same file in Excel 2007 and no problem at all. Have you experienced this problem with 2010?

Paul,

I have to admit, I have not yet bought excel 2010.

Can I add 3rd, fourth and fifth column of criteria such as size and color?

Oscar, I have a problem. I am building this form for my startup company in EXCEL 2003. I have one column with dropdown list with the parts I am selling. In the other column I want the prices to be automatically added to the cell when I select the part from the dropdown. How do I do that?

Thanks in advance

Many thanks to you. That's actually one of the best tips I've ever received for Excel. Works like a charme, even when going for more sheets involved (maybe more tricky then, however, still working with some modifications).

hi Oscar,

would know how this can be done using Form Control?

panda,

See this blog post:

Apply dependent combo box selections to a filter in excel 2007

Dear Sir,

I am very much impressed with use these formulas.

With Regards

[...] -- Dependent Lists zie Excel Data Validation -- Dependent Lists With INDEX voor unieke waardes zie Create dependent drop down lists containing unique distinct values in excel | Get Digital Help ik heb dependent data validation nooit dieper zien gaan dan 3 nivo's als je het met formules wil [...]

hi there - is it just me !! can any one explain why when i type in the bing browser "www.get-digital-help.com" i get a different site yet whe i type it in google its ok? could this be a bug in my system or is any one else having same probs ?

alf saden

Oscar, thank you very much for the help. You have saved me many hours and taught me a lot.

I am having the same problem that Paul is having with 2010. If I try to select the drop down menu very fast in 2010, Excel crashes. In 2007, the "waiting" symbol on the mouse appears for maybe half a second, and then everything is fine. Anyone have any ideas on how to help.

Hello All,

My Question,

I need to create another dependent drop down list based on the first one list? If i change the first list "A" so i will get the results on "B" and "C"

here the link for MREXCEL forum the file is there.

https://www.mrexcel.com/forum/showthread.php?t=610600

please advice.

Said,

If I am not mistaking, I think my answer to Dan answers your question.

Oscar, thank you for the help. still the problem is not solved yet. would you please have a look for the excel sheet see if you can do it there? i have done the first dependent list but still i can't do the second! can you please do it for me??

thank you again.

Said,

Contact form

Hi Oscar,

Thanks for this useful tip.

I have a query for Excel 2007.

I made a drop down list which includes 2 departments. Finance Dept and HR Dept.

In the dependent column, I mentioned the designations within the above 2 departments.

When I select Finance Department, the dependent list shows me the designations in that department and I select one designation in it e.g Senior Accoutant.

however, when i select HR deparment,it continues to show Senior accoutant in the dependent list. I want it to be updated itself and show the top most designation in the HR dependent list.

How do i do that ?

Thanks

Haroun,

I believe this post answers your question:

Apply dependent combo box selections to a filter in excel 2007

Hi,

I have followed the instructions here but have a question. I have a list of Categories and associated Subcategories.

I want to create the dependant drop downs for use on a form people will fill out. On the form they will select "category" via the first drop down and then select an allowed 'subcategory' via the dependant drop down.

It seems when you create the dependant drop down in the last step of your instructions, you are linking it to the first drop down (in your example uniqueorder).

In teh form I am trying to build I want to have several rows that allow people to utilize the drop downs.

I haev tried editing the conditional dropdown to reflect the new location of the first drop down, but this does not seem to work.

Any ideas?

Thanks!

I am having a small issue. This seems to work almost perfectly. They only thing that I can't seem to understand is why it is not returning all the sub categories that I have once we get past a certain number of main categories.

I have a 3 level of categories

Level 1, Level 2, Level 3. Level 1 has 100 entries but when I select the last category (which starts at row #93 it only return the first 2 sub categories when there are actually 9

Any Ideas

Thanks I figured this out. My named ranges were incorrectly configured.

Pierre Letourneau,

I am happy you figured it out!

Vince,

I donÂ´t know how to create three or more dependent drop down lists on multiple rows using array formulas.

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

If you are looking for two dependent drop down lists in multiple rows: Dependent data validation lists in multiple rows

Tks very much for yr tip.

but when I've Created a unique distinct list from column A.

type =INDEX(order,MATCH(0,COUNTIF($A$1:A1,order),0))to cell A2

result N/A

what wrong?

tks

nam,

Array formula in cell A2 (not A1):

Hey, there. This worked like a dream. Exactly what I wanted. So, I have a row with 3 drop downs, with the 2nd cell dependant on the first, and the 3rd being dependant on the previous 2. How do I duplicate this onto another row without having to create another data/list sheet and calculation sheet for every duplicate row I want to make?

Dear Mr. Oscar

thanks very much

it works

Very clear and helpful. I echo Sharmila's post. Can't thank enough.

Sandi B,

I am happy you like it! There is an alternative version with no vba:

Dependent data validation lists in multiple rows

Oscar,

Thank you for such an informative discussion on this topic. I have a question on the drop-down lists. Once the coding is done as shown and I tried it on two sets of data, the user cannot input a value from his own. He is only restricted to those values. What if I want to have a drop-down list and as well the user can input his own value i.e., a custom value.

I am trying to make a calculator where the bolt sizes can be selected from a drop-down list in a cell but also one can input his own bolt size in the same cell. What changes would I need to make to the code?

Thanks in advance.

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that contains my data and I want to generate a drop down list based on the input from one column. Which I already have.

Once the user selects the value from the drop down, I want it to generate another unique list just in a column.

For example: the sheet contains store numbers (one column) and employee names (a second column), which may be listed multiple times. I want to create the drop down for the store number and then creating a list of employee names (non-repeating) that isnt a drop down, just a standard list is say A1,A2,A3,A4,etc

Is that clear as mud?

Thanks.

Will,

Yes, I think I understand. I have to use an udf to concatenate values.

Check out this file:

Will.xlsm

The dependent drop down list macro works like a beauty when working with small quantities of data :-)

However, my plan was to use it for the user to specify valid product configurations when specifying sales forecasts (about 1.000 - 2.000 product forecast lines). The product structure include 3 levels;

1) Product Line; (Currently 3 product lines)

2) Product Series; Within each Product Line there are a number for Product Series (about 10+- Product Series within each Product Line

3) Product Model; There are multiple Product Models (8 - 20 Models pr Series) within each Product Series.

A typical forecast entry line looks like this;

Customer / Project, Product Line, Product Series (within p.line), Product Model (within Line, and Series) [I use dependent drop down lists for this] Multiple lookups from a product table looking up selected product attributes including price etc.

As stated above, it works like a beauty with a 10 - 30 lines, it gets very slow when getting close to 100 and unacceptable (and I have experienced even full breakdown) after 150 -- fare from target 1-2.000 lines.

Please advice.

Dag

Dag,

Perhaps you have more cpu intense formulas in your sheet?

Hi Oscar,

I'm trying to use the above information (very helpful by the way) to create an order form for products; whereby a customer can select the type/category, and then subsequent information after that. The problem is that I cannot get my head around how to arrange the drop-down boxes & the information.

I would like to hide the product information in a sheet, and have the order form in another.

I understand the above example is to be used to retrieve entered information for record keeping - is there any way I can create a form?

Regards.

Callum.

Hi Oscar.

Great site! I've worked with this today and as long as there is more than one entry in a list then the dependant list works well.

I have 3 columns with 2 dependant lists

Column 1 = Category 1

Column 2 = Category 2 Which can have from 1 to 25 items depending on the selection in Category 1

Column 3 = Category 3 Which can have from 1 to 25 items depending on the selection in Category 2

If category 2 has only one item then only errors are displayed in Category 3 even if there are values.

I am filtering these lists from an Excel Table

I've looked at the formulae, but I can't work out which bit to amend. I think that the problem lies with $A$1:A1, but I don't really understand the syntax. Could you explain further please?

Kind regards

Philip Smith,

I recreated your setup with the attached file, using one item in category two. It seems to work.

$A$1:A1 is a cell range that automatically expands when you copy the cell (not the formula). $A$1 is an absolute cell reference, it does not change. A1 is a relative cell reference, it changes.

Copying the cell to the next cell below changes the formula to $A$1:A2.

[...] have the information you want. I can't remember exactly where I found the advice, but this link https://www.get-digital-help.com/2010...lues-in-excel/ should help. Your requirements are entirely achievable. Regards [...]

I have a list where not all the cells are populated. Tried the method above but the unique list appears to be incomplete (e.g missing values). Any way to make this work for me. Really appreciate your assistance.

Example:

List = A1:A20

Populated = A2:A10,A15:A17

Not Populated = A1,A11:A14,A18:A20

Ahmad,

Did you adjust the cell reference (bolded)?

Array formula in cell A2, sheet 2:

=INDEX(order,MATCH(0,COUNTIF(

$A$1:A1,order),0)[...] into a unique list that is organized. I've used the index function (similar to what is used in Create dependent drop down lists containing unique distinct values in excel | Get Digital Help - Mic... ) to do it, however, because I'm using 1,000+ lines about 50+ uniques in a single column, it tends [...]

Thank you Oscar!

exist too solution for google spreadsheet? everything works, but I have problem only with the application to next rows.

Sievert,

I have problem only with the application to next rows.Can you explain in greater detail?

Please Oscar, look at the link - https://www.mrexcel.com/forum/excel-questions/669408-dependent-drop-down-lists-google-spreedsheet-values-multiple-rows.html .

I tried to more explain.. Thank you

Sievert,

Only one thing doesn't work Application function to the next rows.. I tried use timestamp, but without successful, maybe I used wrong it.I still donÂ´t understand and I donÂ´t know much about google spreadsheets.

Sorry, my mistake..

When you open spreedsheet :

https://docs.google.com/a/linksoft.cz/spreadsheet/ccc?key=0Asiaoa2t4PpGdGJDWk5wSE43LVdyTXJHU3R0SHdkY0E#gid=1

look at the row 2, dependent list works correctly, when you look at the row 3, dependent list doesnÂ´t work.. because now you can see values B1 and B2... but still you can see values from the previous row 2... so function works only for second row and the multiple rows doesnÂ´t work :(

[...] drop down lists containing unique distinct values in spreedsheet according to the instructions ( Create dependent drop down lists containing unique distinct values in excel | Get Digital Help - Mic... ) , everything works like in excel. Only one thing doesn't work Application function to the next [...]

[...] what was selected first: picture hosting So I got that working using instructions found here: Create dependent drop down lists containing unique distinct values in excel | Get Digital Help - Mic... The method shown there does work, but only for that particular row. If I copy it down, it won't [...]

This is exactly what I was looking for. I am just trying to understand how it works. I have understood what is going on up to the point where you use the function IF(Sheet2!$B$2:$B$1000="", "", 1), I can see that it returns a 1 into an array for cells that are neither blank nor #N/A, but why does the test ="","" do this.

Matthew Gavanda,

why does the test ="","" do thisGreat question! I donÂ´t know why it does that.

COUNT(IF(Sheet2!$A$2:$A$1000="", "", 1)) counts cells that are neither blank nor #N/A

COUNTA(Sheet2!$A$2:$A$1000) counts ALL cells except blanks

Hi Oscar,

I am following your steps and I am now on "Create a unique distinct list from column A" topic. When I do the CTRL + SHIFT + ENTER in the cell with the formula "=INDEX(order,MATCH(0,COUNTIF($A$1:A1,order),0))", Excel opens a Window with the "Update Values: Sheet1" title. When I close that window, the function returns the value #VALUE!

Do you know what I could be doing wrong?

Thank you and regards.

Rejane

order - is a named range. If that named range is not in your spreadsheet, the formula wonÂ´t work.

Open the attached file in this post and check it out.

Hi Oscar,

This formula works fine with small tables and where data in a column are of the same type (either numbers or text).

Can you suggest a solution for the kind of cases I mention above, except using VBA?

Thanks,

Eduard

Eduard,

Large data sets?No.

where data in a column are of the same type (either numbers or text)As far as I know, the formula extracts both numbers and texts combined? Can you provide an example?

Hey Oscar,

When I select my combo box to choose a value, the #N/A is there mutiple time. Maybe i did a step wrong. You have any idea?

Thanks !

I forgot I had another question XD. In your example, I understand that you can only have one combox box, but I want a column full of combobox and on the second column the combobox filter with the value select in the combobox in the first column of the same row.

Sr for my bad english, hope you understand!

Max

I find in the comment another example for what im looking for. But I still have my first problem with the #N/A

Max,

Hard to say, upload an example file.

Hello,

Maybe this is a silly question, but i have been trying to put the Dropdown lists on Sheet1 in Sheet3. When i do that i update the 2nd in sheet 2 to sheet 3 ( =INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(orderSheet3!$D$2), 0))" + CTRL + SHIFT + ENTER) But i keep getting NV# is there something im missing?

Best regards,

Miguel Araque

I was just able to make it work !,

really good website!!

Regards

You are a genius I fan you

I want to be like you

can You OPEN and LOCK writing in columns and rows by ( DATA VALIDATION )

How To Lock Or Protect Cell Using Data Validation?

Excellent work. thanks for sharing.

If we dont want to use array fomular

maybe this is a choice

INDEX(order,MATCH(0,INDEX(COUNTIF($A$1:A1,order),),0))-->enter only

Dear sir, i need ur urgent help,

how i can use this dependent drop code in vba form of combobox.

please send me module code of that.

THanks

Sir i need it very urgent

Thank you for the wonderful tips!

I tried to edit your example with no luck...

in sheet 1, i have for example these:

A2: John, B2: Supermarket shoes

A3: John, B3: Bowling

A4: Maria, B4: Groceries

A5: Maria, B5: School

etc.

I use my own values, but the in Sheet 2, in column B i get for all values #N/A...

What exactly am i doing wrong?

Thanks again for your lovely work!

Haris Eliades,

You are right, you get #N/A when you use your values in my example.

Try changing the value in the drop down list in cell D2, sheet1 and the array formula in column B, sheet2 returns values again.

The array formula in column B, sheet2 returned #N/A because the value in cell D2, sheet1 (1010) was not found in column A, sheet2.

Hi Oscar,

The dependent drop down list is perfect for a race report sheet I am developing for my sailing club (see website link). I want to be able to have the user select the race date (equivalent to the order in your example), and then select the race name (equivalent to the product). Our race calendar is typically two races per day, with a results report to be generated for each race.

I originally had my race calendar table formatted as a table, which meant when creating the first dynamic named range, Excel was changing the range in the formula to the table name (e.g. RaceDetails[@Date]). When I converted the table back to a normal range, the error was removed. Is there an adjustment to the formulas above that would allow the same result using a table range?

I'll also note that if the values in the original list contain formulas, the unique list also won't work correctly as Excel thinks each value is unique - overcome by copy and paste values into that range.

Cheers!

Mark Graveson,

Is there an adjustment to the formulas above that would allow the same result using a table range?

I have created an excel defined table and changed formulas, get file:

unique-distinct-dependent-lists1-three-columns.xlsx

Thank you for providing this! This is awesome. I did run into an issue though which I thought you might be able to help.

Whenever I save the workbook and reopen, the drop down only disaplays first two items. All the formulas are there, and I have to go into the Data Validation for each drop down and (without doing anything) just save it to re-enable the drop down.

I dug through google and didn't find much except that there is a known bug(?) which Microsoft is releasing a fix. Have you run into this issue? I do have rather large drop down (about 380) - could that be an issue?

Thank you very much for all your help!

Please disregard above question. I used the Defined Names, and the problem is resolved. Thank you again!

WJ,

Thank you for posting the solution!

Thanks so much for this-- it's been really helpful and worked perfectly for my lists with straight text entered.

I was also hoping to do the same with a list where cell values are based on CONCATENATE (from three other cells, two with names and one with a date in order to generate a unique name). I'm assuming that the above method doesn't deal with cells with forumulae (I'm returning a list of blank cells).

Is there any way for me to create an alphebetized list based on the values of these cells?

Hi there,

Kindly help me to get rid of few iss

I want to two simple dropdown list join but i cannot,

Suppose,

1st drop down list cell(A2) and second (B2)

A2=List(Samsung,Nokia)

B2=(List of samsung and list of nokia mobile name)

When select(A2) from dropdown list samsung after that B2 Show Relavent Samusumg mobile list,as same as Nokia..

So how can i join it....

Plz help me..

hi Oscar,

how can i store multiple drop down menu results onto a separate spreed sheet and keep adding to it

scott,

Can you explain in greater detail or provide an example workbook?

Hi Oscar,

I have created 2 separate wook books, one containing the lists and the other containing the categories containing the lists in the drop down format. when you select the chosen item from the drop down menu I would like to be able to press on a submit function and store the results from several lists onto a separate work book with the ability to reset and continue adding more results

thanks

Scott

[…] Perhaps there is a better solution, but if sorting the data is not an option, you may need to create a 2nd, automatically updated list, based on the first one - that also automatically sorts alphabetically. See example given in the link below Create a drop down list containing alphabetically sorted values in excel | Get Digital Help - Micros… […]

I am having trouble making the code reference different cells. My lists are not in column A and do not start at Row 1. What values in the code do I change to reference different cells?

Sometime when I paste the code for your saple to my new file, it will not function. Do you have to turn on or enable something, or is there a glitch in MS Excel program.

I think this formula is really cool. I used it in my excel tool and works great.

Does this not work for lists with both alphabets and numbers?

Seems to only work for lists that are either all words or all numbers.

Hi Oscar,

Very useful indeed! thanks so much! I need it to work for 4 not 3 columns of data. Would it be possible to post an example file for this?

Many thanks

Rob

Thank you very much. So clear and very well described. Hours saved.Great help.

hello Oscar my question is on the example you made in Step number 8 , where you have a drop down menu in the bottom for anyone to choose the region, how can I make the drop down in the left to control the other cells in the same row , for example if I choose South America in region in that drop down menu, immediately the column C and D will auto populate giving me Brazil and Sao Paulo, without me having to go and press with left mouse button on the drop down menu and choose between the options one by one?

thanks in advance

john

Oscar,

superb implementation - still trying to understand it completely.

I am just using the Create a dynamic named range/Create a unique distinct list from column A/Create a dynamic named range to get unique distinct list components.

but I have 1 difference: my equivalent of col A and 'order' is implemented in an excel 'table'. it works fine. I can add/delete rows to the 'table' and it expands/contracts as expected.

however, 'uniqueorder', implemented as you describe, works just 1x. if I add/delete rows in 'order', in the excel 'table', then the worksheet with 'uniqueorder' does NOT expand/contract similarly.

is it possible that defining 'order'/'uniqueorder' to the name manager using the OFFSET function is in conflict with the excel concept of a 'table'?

anyway, thanks for getting me this far in my learning curve.

ron

ps: just 1 last question 'order'/'uniqueorder' start in row 2. now, defining the distinct list using the index function with COUNTIF($A$1:A1,order) also starts in row 2. [see how wet I am behind the ears?] why does the COUNTIF 'start' in row 1?

ron omegna,

is it possible that defining 'order'/'uniqueorder' to the name manager using the OFFSET function is in conflict with the excel concept of a 'table'?You can create a named range but do not use the same name as the table name.

ps: just 1 last question 'order'/'uniqueorder' start in row 2. now, defining the distinct list using the index function with COUNTIF($A$1:A1,order) also starts in row 2. [see how wet I am behind the ears?] why does the COUNTIF 'start' in row 1?You canÂ´t use a cell reference to the current cell, it creates a circular reference. Try it out, Select cell A1 and type =A1. You will get a circular reference warning.

Hi Oscar,

I utilized your sample file because I wanted to add a 3rd and 4th column. However, the dependent drop-downs that I created (using the same array formulae and named ranges as the 1st and 2nd columns) do not work. The new columns just do not show any value.

Is this due to the VB only firing once for the value in the first column?

Happy to upload my sheet to explain further if needed.

Many thanks!

[…] Dropdown lists (multiple) I have checked below link, but the Drop Downs Position is fixed Create dependent drop down lists containing unique distinct values in excel I have a requirement here like, user can enter a number say 4. and there I need to insert 4 […]

This is a great formula but I have hit a problem with Excel 2011.

I have a list which will be constantly added to and I've used your formula to duplicate the list and sort it alphabetically. Then I have used your solution to create a data validation drop down menu from the sorted list. The drop down not only shows the entered values in my list but also shows the blank cells which have yet to be populated. How do we remove the blank cells from the drop down without changing the range of the drop down.

Oscar, on the off chance that you still check this, I can't seem to get my second unique list to populate. I built from scratch following your example, and the 1st unique list is fine. My data also seems to be in a similar order as yours. I have 2 columns, "Category" and "SubCategory." I have about 30 "categories" and 60 "subcategories," - some categories only have 1 sub category, some have 15. The first cell of the 2nd unique list seems to populate correctly, and after that I get #N/A. My cell references (i.e. $B$1:B1) doesn't seem to be the problem. When I try to evaluate, excel says cell B3 would evaluate to =INDEX(correct "Category" cells,MATCH(0,{1,1,1,1,1,1...},0)). There's about 30-40 1s in there. Any idea what this means? Unfortunately I can't upload, as it's on my work computer - thanks for getting me this far!

Side note - not sure if this is relevant or not, but some of my "sub categories" are equal to the "category" - this is a breakdown of business functions, so I have something like

Category Sub-Category

Acquisition Acquisition

Marketing Advertisement

Marketing Offer

Marketing Commercial

etc. Thanks again!

hi ,i am creating a dependent drop down list with values in it , i found different links for the same but they are not so sufficient. now in my drop down list the first list is in column B having values which are in percentage link 100%,90%,88%,75% and in column E their are another list having error code like 2A,2B,2C,2D ,and for the column g their are error code description like options type incorrect,incorrect builder's (i can use vlookup here to found out what description stand against the error code like 2A=options type incorrect,i think i can do this part) now here the problem starts first i serached on google and found out this link https://www.contextures.com/xlDataVal02.html ,in this link their is a process to name the columns in the name box as they are my value based so iam unable to name them ,(we can just write alphabets only in the name box ,then i googled again started forum at for https://www.mrexcel.com/forum/excel-q...ml#post4153088 ,nice guy told me to put "A" in front of your cell but this is not the way i want if i put the alphabet then after giving them scoring i again need to replace it. i again googled and found this link https://www.get-digital-help.com/2010...lues-in-excel/, this was really was of my work i am unable to understand it as it is so advanced for me right now. i like the way where you just name them and get started but you know it won;t accept numbers and i want to number them only. link at get digital is exactly as i need but so difficult ,is their any other way to do with ease ,if not please explain me the get help forum and help me to create this sheet . i am unable to attach right now my sheet.

Thank you for the formula, it helps a lot! However,I have the same problem as Dan above. Do you have any suggestions on how to remove the cells with no text("blank")from the drop down list?

Any chance someone can tell me how to convert

=IF(COUNTA(List)>=ROWS($A$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "")

to a standard formula? I cannot use array formulas in a shared workbook

Oscar:

I was fortunate to come across your page describing a method of creating sorted, dynamic data validation lists for Excel. The implementation was straight-forward and I was able to make it work in one instance on my workbook.

Unfortunately a VERY similar instance within the same workbook refuses to function, and I've been unable to determine why. In the latter case the "List" range works as expected, and the array formula returns the correct NUMBER of rows, but the values are either being repeated or truncated.

Any assistance will be appreciated. I am able to share the workbook with you if interested.

Matt

Hello Oscar,

I was very happy whenI saw your solution for an issue I face regularly. However...when I enter my data in your example worksheet, it doesn't function anymore. My data looks like '6000748, '6000845, '14000456 etc. So data is entered as text, like your data is. Neither does it function when I use your formulas and name definitions in my worksheet.

Do you have any idea where it goes wrong?

Kind regards,

Rens

Hello again,

I found out that the formula works when I precede the data with a letter, so 'D6000748, 'D6000845 etc. For now I have build a workaround in which I use three columns (add "D", Sort, remove "D") instead of one.

I suppose this might have the same cause as the problem I often experience in Excel when I have to Vlookup numbers stored as text. For that issue I also haven't found a real solution yet.

Regards,

Rens

Hello Oscar,

I'm not sure if you're still monitoring these older posts, but I figure I would send along a thank you for your wonderfully clear tutorials.

I'm working through adapting this for a third column. I am able to successfully work with a third column in the 'Multiple rows' sheet where column B only has one category in the dropdown. For instances where there are multiple in B, they cease to function. I'm wondering if it is something as simple as refining the VBA code to account for the added range (no longer "A", but "A:B")?

I hope this message finds you well. Any help would be much appreciated. I'll continue to dig and will be sure to upload the solution if I figure it out.

Thanks again for all your posts. They are great.

Your efforts, continuing along from the first posts on this topic, have helped me very much and saved a ton of time. Thank you for making all of your work available!

Michael Norelli,

thank you!

Hi, Oscar:

Excellent way to do the job. But it requires an extra list(or table) on the same sheet or other sheets (even other Workbooks. Is there a way to accomplish the same task without using extra list(or table)? (Or just Mission Impossible. Maybe VBA can do it.)

Thank you very much!

JOE

Yes, you can use a user defined function to sort the values and then display them in a regular drop-down list, this article demonstrates that it is possible:

https://www.get-digital-help.com/2012/08/27/use-filtered-table-values-in-a-drop-down-list-vba/

or this macro:

https://www.get-digital-help.com/2011/10/05/add-values-to-a-data-validation-list-vba/

I believe this article has a workbook that contains a macro or udf that sorts values:

https://www.get-digital-help.com/2011/04/21/excel-udf-combine-cell-ranges-into-a-single-range-while-eliminating-blanks/