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 dropdown lists.
The first drop down list contains unique distinct values from column A.
The second dropdown list contains unique distinct values from column B, based on the chosen value in the first dropdown list.
Watch a video on how to set up dependent dropdown 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.
 Click "Formulas" tab
 Click "Name Manager"
 Click "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.
 Click "Close" button
Recommended article
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:
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference [โฆ]
5 easy ways to extract Unique Distinct Values
Create a dynamic named range to get unique distinct list
 Select Sheet2
 Click "Formulas" tab
 Click "Name Manager"
 Click "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.
 Click "Close" button
Recommended article:
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a [โฆ]
How to use the OFFSET function
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
 Click Data tab
 Click Data validation button
 Click "Data validation..."
 Select List in the "Allow:" window.
 Type =uniqueorder in the "Source:" window
 Click OK!
Here is a picture of what we have accomplished so far.
Recommended article
Add new items to a drop down list automatically
A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you [โฆ]
Add new items to a drop down list automatically
How to create a secondary unique list based on only one chosen cell value in first drop down list
Create a dynamic named range
 Click "Formulas" tab
 Click "Name Manager"
 Click "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.
 Click "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
 Click "Formulas" tab
 Click "Name Manager"
 Click "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.
 Click "Close" button
Create drop down list
 Select Sheet1
 Select cell D5
 Click Data tab
 Click Data validation button
 Click "Data validation..."
 Select List in the "Allow:" window.
 Type =uniqueproduct in the "Source:" window
 Click OK!
Download example workbook
unique distinct dependent lists.xls
(Excel 972003 Workbook *.xls)
Download example workbook with a third column of data
uniquedistinctdependentlists1 three columns.xls
(Excel 972003 Workbook *.xls)
Recommended articles:
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a dropdown list with unique distinct alphabetically sorted values? Table of contents Sort values using [โฆ]
Populate drop down list with unique distinct values sorted from A to Z
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 [โฆ]
Create dependent drop down lists containing unique distinct values
Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple [โฆ]
Create dependent drop down lists containing unique distinct values in multiple rows
Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on [โฆ]
Invoice template with dependent drop down lists
Dependent drop down lists โ Enable/Disable selection filter
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know [โฆ]
Dependent drop down lists โ Enable/Disable selection filter
Dependent dropdown lists in multiple rows
This article demonstrates how to set up dependent dropdown lists in multiple cells. The dropdown lists are populated based on [โฆ]
Dependent dropdown lists in multiple rows
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 macroenabled 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 AddIn
How to use the AddIn
 Go to tab "AddIns"

Click "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
 Click "Next"

Copy code to a module

Click Close
[/expand]
Purchase Dependent Drop Down Lists AddIn for Excel 2007/2010/2013  Price $19 USD
Questions
Is there a money back guarantee?
Sure, you have unconditional 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 addin. However, you canยดt copy and paste the dropdown lists yourself, they have unique names.
What customizations can I do with a combobox (form control)?
You can change the number of drop down lines, 3d effects and link each drop down list to a single cell.
Can I hide the pivot table sheet?
Yes! Right click on the sheet and click on "Hide"
Can I change the order of drop down lists?
Yes, rearrange the table columns and then use the AddIn to create drop down lists.
If I construct a UI using the AddIn, then will anyone be able to use those on their own computers without adding the AddIn themselves?
Yes, they will be able to use the drop down lists.
If so, then would they just need to enable macros?
Yes, that is correct.
Testimonials
The addin makes dependent dropdown lists easy and flexible to implement in Excel. It fills a vital missing gap in Excel's functionality, because the dropdowns 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 dropdown lists is still so difficult in Excel? The Dependent Drop Down Lists addin 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 download page. You will also receive an email with the download link.
 You have five attempts to download the file.
 The download link will expire in 120 hours (5 days).
If you canยดt downloading the file, contact me.
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me [โฆ]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: [โฆ]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete [โฆ]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. [โฆ]
Dependent dropdown lists in multiple rows
This article demonstrates how to set up dependent dropdown lists in multiple cells. The dropdown lists are populated based on [โฆ]
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.getdigitalhelp.com/2010/11/16/createdependentdropdownlistscontaininguniquedistinctvaluesinmultiplerows/
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
on click of an alphabet its should move to the the corresponding list
Its working very well...........but i want it like this
on click of an alphabet its should move to the 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,
Download example workbook with a third column of data
uniquedistinctdependentlists1 three columns.xls
(Excel 972003 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.getdigitalhelp.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 dropdown 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 dropdown 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 dropdown 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 (nonrepeating) 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 12.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 dropdown 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.getdigitalhelp.com/2010...luesinexcel/ 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 [...]