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)
Recommended articles:
Recommended articles
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Recommended articles
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Recommended articles
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Recommended articles
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
Recommended articles
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Recommended articles
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
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Â
- 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]
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 […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Excel categories
113 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.
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.
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.
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
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 [...]
[...] 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 this
Great 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
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
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
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.
[…] 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 […]
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!