Comments on: Making a dependent (cascading) drop down list in Excel

The tutorial demonstrates how to create cascading drop down lists in Excel that display choices depending on the value selected in a previous dropdown. Continue reading →

Comments page 5. Total comments: 329

  1. Thank You for the article!!

  2. Hey,

    I was just wondering how I could add a third dynamic cascading drop-down, based on options that show up in the second drop-down.

    Thanks

  3. Hi,

    How would you set this up by having attributes running on rows rather than over columns. The dropdowns need to run for new rows added.

    Regards

  4. Hello,

    I have two questions:

    -I followed the "Exclude blank rows from the dynamic cascading dropdown" instructions and I don't have blanks, but the title/header is in my second dropdown box. Is there a way to not have it?

    -I am also working with 5 variables. Is there a way to reference a complete table for each dropdown? Or do I have to make independent lists for everything?

    Thank you!

  5. Say we have 2 Cells C7 & D7 each have a Data Validation. Well if we change the value of the Cell in the C7 then the Value of the Cell D7 should also Change according to the corresponding List of C7 or turn blank. But the value of D7 always remain the previous value of Data Validation changed in C7. We have to Change D7 manually later after changing C7. Can it be possible without VB.

  6. Hi,

    I have a table in which I wish to use each column to restrict the choices for the next column, which restricts the choices for the next column, and so on. A lot of the information in each column is repeated, rather than being unique values as listed here. Can you possibly help with this?

  7. I have a table containing first two columns having two indipendent drop-down lists. I like to have a drop-down list in the third column depending on the values of the first two cells.
    Please advice.

  8. I made for myself a simpler 2 step solution for detecting primary and secondary dropdowns that mismatch due to changing primary after secondary has been selected:

    Step 1: start primary and secondary choices with matching numbers i.e., primary choices 1 - Apricot, 2 - Mango, 3 - Oranges; secondary example 2.1 - Philippines.

    Step 2: Use conditional formatting to make the secondary choice cell change color (say turn bright red) if first left digit of primary not equal to first left digit of secondary, i.e. 3 - Oranges with 2.1 - Philippines, 3 ≠ 2, secondary choice cell's background turns red.

    We can also use conditional cell formatting to show a glaringly different color if no choices have been made yet.

    Jossy

  9. Thanks for this post, this is a really interesting approach. I use Excel tables and pivot tables quite often, and so a cascaded dropdown set would be most useful as the validation for a pair of table columns.

    So as an example, say I had a second table to collect fruit inspections with columns:
    Inspection Number | Fruit Observed | Country of Origin | Inspection Result

    I'd like to put the Fruit dropdown in the Fruit Observed, and have that cascade to the Country of Origin field on the same row.

    Is this feasible?

    Jeff

  10. totally awesome! Thanks a lot for this article

  11. Great article .. good explanation and easy to follow instructions.

    While the cascading dropdowns works well without VB, it has one major drawback !
    One is NOT able to enter a new value from dropdown, until all sub categories( dependant dropdowns) are cleared.
    However one is ABLE to delete or paste a new value, which is a real pity.
    Would be nice if that loophole could be blocked.

  12. Got it working, removed the spaces flanking the colon in the subgroup_list2 formula!

  13. Great article! Helped a lot!

  14. I thoroughly enjoyed this tutorial, thank you so much for the time and effort it surely took to create!

    I did run into an issue with the "dynamic cascading dropdowns with no blanks".

    When I enter the ..._list2 Source in the Data Validation window I get a message stating the Source evaluates to an error. My list uses groups and subgroups rather than fruit and exporters.
    Here are the extra names I created for the no blanks formulae:
    subgroup_list2 =INDEX(groups_tbl,1,col_num) : INDEX(groups_tbl,COUNTA(entire_col),col_num)
    col_num =MATCH(group,group_list,0)
    entire_col =INDEX(groups_tbl,,col_num)

    Kind regards

  15. HiSvetlana Cheusheva
    You are genius. Thanx a lot for such a wonderful topic in excel. I love excel.

  16. Hello Dear,
    I create a drop down list of 3 steps. Which are thana, market & outlet. I could complete it 2 steps but can't complete last step, means when I click thana it shows his market what I setup but when I Click market it doesn't show his outlet. How do I solve this?

    1. Hello,

      if I understand your task correctly, it looks like you forget to create the third drop down list. Or did something wrong when creating one.
      Please take a closer look at the fifth point of the article above - 5. Add a third dependent drop-down list (optional). I kindly ask you to follow the instructions described there.
      If this doesn't help, I'm afraid we will need more details on your task, lists for drop down, etc.

  17. Hi,
    I need some help.
    Turkey is a common exporters, how could I do to add a third drop down list based on the exporters?
    For eg, Mango> Turkey> northern Turkey / southern turkey
    apricot> turkey> central turkey / northern turkey

    such that, central turkey is not a option for mango

    how and what should i do such that third drop down list is dependent on the second drop down list?

    Kindly assist

    Thank you

    1. Hello,

      please take a closer look at the point of the article above saying Add a third dependent drop-down list (optional). There's a tutorial with example pictures that should help :)

      1. Hi Natalia,
        I understand on the Add a third dependent drop-down list, however, my problem is 'Turkey' is a repetition. I have a issue such that, if I select Mango> Turkey> I only want 2 choice either North/South. And if I select Apricot > Turkey> I only want 2 choice either North/Central.
        Because 'Turkey' is a repetition as a dependent list, I could not link the 3 dependent list

        Please assist what can be done

        1. Hi,

          could you please send us a small example workbook to support@ablebits.com with the link to this comment?
          Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.
          We'll look into your task and try to help.

  18. Hello everyone.

    I am having trouble finding an error in a drop-down dependent worksheet I have created.

    My worksheet is formatted so that a user would select in column B one of 6 choices. This is working as intended.
    In the next column, C (to the right), a drop-down box with three different choices dependent on the selection in column B should populate. I intended for this process to be copied exactly the same way in the rows beneath.

    The first cell available in my worksheet for the first choice is B8. The cell next to it with the drop-down dependent selection is C8. This is actually working.

    In the rows beneath this, column B is working correctly. The error I cant find is that, say you select a different choice in column B below B8, the dependent drop-down boxes in the rest of column C only show the choices for B8's options. Its as if the entire column C is only checking B8 for the dependent drop downs.

    Here are all the formulas I am using:

    category_list: =INDEX(standards_tbl,,MATCH(standard,standards_list,0))
    Print_Area: ='Standards Journal'!$A$1:$D$26
    standard: ='Standards Journal'!$B$8
    standards_list: =standards_tbl[#Headers]
    standards_tbl: =Sheet2!$b$3:$G$5

    I have tried updating the "standard" formula to include the entirety of column B but this doesn't seem to fix the issue.

    Any ideas what I am doing wrong?

  19. Please help!!!
    I want to extract the list(data validation) from the data as below. First lookup the PO# and then create a list of corresponding distinct values. First column named as "PO#" and the second column is "Category".

    PO# Category
    KIPS/IT/01 CPU
    KIPS/IT/01 LCD
    KIPS/IT/01 LCD
    KIPS/IT/02 Cable
    KIPS/IT/02 CPU
    KIPS/IT/02 LCD
    KIPS/IT/03 Connector
    KIPS/IT/04 CPU
    KIPS/IT/04 LCD
    KIPS/IT/04 Cable
    KIPS/IT/04 Connector
    KIPS/IT/04 Mouse
    KIPS/IT/05 CPU
    KIPS/IT/05 LCD
    KIPS/IT/05 Cable
    KIPS/IT/06 CPU
    KIPS/IT/06 LCD
    KIPS/IT/06 Cable
    KIPS/IT/06 Connector
    KIPS/IT/06 Mouse
    KIPS/IT/07 Webcam
    KIPS/IT/07 Toner
    KIPS/IT/07 Miscellaneous

  20. how to put these conditions
    E5>1 AND G5>1 THEN YES OTHERWISE NO
    IF E52 THEN YES OTHERWISE NO

    1. Hello, Tannu,

      I believe the IF function together with AND can help. Please tale a look at the article explaining how they works here.

  21. Hey All,

    I am tryig to develop 30 lines in which the user of the excel file can put only certain predefined values. Dependent drop-down lists seem to be a workable solution, but due to structure and dynamics of the data base I cannot apply any of the formulas/codes so far. I am much obliged or any help. Here it is the back ground

    I have a database in a worksheet where:
    - in column A is list of all producer; and
    - in column B next to the producer's name is included list with this producer's products Products are listed in column B and ocupy 3 to 7 cells from column B
    - 1 row contains always only 1 cell with value inside. Either in column A is the name of the producer, or in column B is a product, which is produced by this producer.

    By way of example:
    - Row 1 is for headings;
    - A2 - Producer1;
    - B3 - Product1, B4 - Product2, B5 - Product3
    - A6 - Producer2;
    - B7 - ProductX, B8 - ProductY, B9 - ProductZ
    etc.

    Could someone please help?

  22. What would you do if your "fruit" was listed in the first column of your table instead of your table headers?

    For example:

    Fruit: Country:
    Apricot Algeria
    Apricot Iran
    Apricot Pakistan
    Apricot Turkey

    Ive used your index match method, but placed the match function in the "rows" instead of "columns". However, my drop down list for Country that is based on my Fruit selection is only returning the 1st result. In the example above it would only return Algeria instead of a drop down list of Algeria:Turkey.

    1. Actually, figured out a solution using a similar approach to your remove blank cells approach "=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)"

      I use the above to return the 1st row where the chosen country appears and the last row where the chosen country appears.

      1. Hi Kevin,
        I have the same requirement as yours where i have the values in 2 columns instead of table headers; Can you please help me with the formula to be used for 2nd dropdown when i select a value in the 1st dropdown.

  23. I have two drop down lists in two different sheets of the same workbook. When I change the value in one dropdown, the other dropdown also should display the same value in the second sheet and when I change value in the second sheet the first one also should change to the same value. Both of the lists contain the same values.How can I do that? Would you help me please? VBA macro would be better for me.

  24. Hello,

    I have successfully created multiple drop down lists for 12 different categories each having 5 options/choices. I used an index-match function to create scores for each category based on what was chosen and then added all categories to receive a total score. My problem is I can't find a of being able to have another drop down list with the customer name where when you select any customer their scores and selections in the other drop down lists would automatically appear/save.
    Thanks!

  25. I have successfully used the example for Dynamic dropdown menus (thank you) and I would like to know if is possible to adapt to fill-in a table with X-Y range of cells where the selection may applied for for any of these cells. So far I can only make it working for rows or columns (take off the "$") in the reference cell setup (fruit in your example).
    Many thanks

  26. Hi!

    Thanks for the usefull information. This explanation is awesome when using just one dependent dropdown. However, I'm working on a sheet with multiple dependent dropdown (B1 is dependent on A1, C1 is dependent on A1 and B1, D1 is dependent on A1, B1 and C1,....

    Any advice on how I should do this?

    Thanks a lot!

  27. How do you create a dependent drop down list for text with space and character such as "/" or "&"?

  28. Hi terry, Please help me, In datavalidation dropdown list which is open by click on mouse but I want to open dropdown list when we select, datavalidation list shouldbe show the option, thank you.

  29. Course| Trainer|Date|Start time|End Time|Duration|Outcome|Notes

    for example the above are rows, is it possible if i select course and it give me all the details horizontally? for example In the Course section I select Employee training, and it give me trainer name, date of training, start time etc?

  30. Hi, this post is really awesome. I have 2 questions:
    1. Is there a way to reference the INDIRECT formula to another workbook? This means all my data lists for the dropdown boxes are in a different workbook. Is this possible?
    2. For the dynamic dropdown with no blanks, is there a way to make it work when the cells are not truly blank? Meaning, they have formulas in them but do not return results so there's no display in the cell but it's not technically blank.
    I've been stuck with these for days.
    THANK YOU SO MUCH!!!

    1. Hello, Kai,
      1. yes, there's a way to reference another workbook for dropdown lists. Please, look at these example pics;
      2. replace Named range counta(entire_col) with ROWS(entire_col)-COUNTBLANK(entire_col) in your formulas.
      For more details go here

  31. Hi, Is there a way to reference a single primary drop down list....twice? For example if your primary drop down selection is named "vehicle" and one of those selections is "bicycle" then when you select "bicycle" there may be reason to have two non-dependent drop downs like "derailleur type" and "tire size" that would both populate when "bicycle" is selected. The problem I run into is that a named range must be unique...I can only use the primary drop down name once only.

    Thanks

  32. Hi have a hazard library with 500 hazard which I want to pick in a list and then I have contributing factors, controls, risks, description which I want to cascade I have only managed to be able to select the hazard and the second column contributoing factors in one cell I can't copy down and I cannot get it to copy across for the varying lists. Any ideas??
    Thanks

  33. Svetlana -

    Thank you so much for this great tutorial!

    In case you followed the directions to the letter and it's still not working, you may have made the same silly mistake I did:

    =INDIRECT(A2) refers to the cell in which the first drop down list is located, not the named range. So when you're putting in the second drop down list, you're referencing the cell preceding it. I was referencing the list as it appeared in my reference tab. Silly!

  34. Can anyone help please. I need to create 2 pick lists in excel 2010 for translation service as follows:
    select language from (pick list) and select language to (pick list) and the third box should show me a list of translators based on the first 2 selections from the list only. Thanks Same

  35. thanks for your good site
    how can i copy a dropdown cell to another cells of a column?
    my mean is data validation-list-indirect(A2)
    i want copy this in all cells of the column

    1. Remove the last $ in your formula.
      Sample:
      From $B$2 to $B2

      hope this helps.

  36. Hi Svetlana,
    I'm trying to load a dropdown list based on doing a VLOOKUP in a data table. For example I load a data table with Orders and Items (only 1 database call) where an Order may have multiple Items.
    I want the user to enter an Order # in a cell, then the corresponding Items are loaded into the dropdown list. I presume I want to load the dropdown using some form of VLOOKUP or Index/Match but can't seem to find a solution. Any help is appreciated!

  37. How to use the same formula in consecutive rows?

  38. Hi,
    If i want to create a sheet where i need to use the cascading dropdown in the consecutive rows, how can I implement it?
    For example, my first row i will select a value from dropdown list In cell A2 and dependent drop down is listed in B2.If I have implement the same in A3 and B3 how should I do it?

  39. I followed the instructions in your example exactly for the dynamic cascading list. However when I select a fruit, no dropdown list appears for exporters. I click on the dropdown button and no dropdown list appears. I can't see anything that I've done wrong. I copied and pasted the name from name manager just to be sure I didn't type it wrong. Any suggestions of likely errors?

  40. Its not working if Named Range values are having space. Deleting the spaces its working..Is there any way to achieve this???

  41. Hello,
    I apply the same way and it works for only different values.
    the problem is I have 3 dependent drop down lists
    the first list contains unique names but the second one has repeated words for each name in the first list and the 3 only depend on them.

  42. I have a long drop down list. Is there a way where typing the first letter will start filtering the drop down list i.e. typing "a" will show only list items starting with "a", continuing typing like "ac" will only show list items starting with "ac" and so on?

  43. Dependent drop down is not working for multiple sheets. Please help

  44. Hello,

    I have a question, I have a dropbox with 2 options. Let's say A and B,
    when I select option B I want to apear one table with prices. And option A have already it diferent table in the main excel sheet.
    I tried but I could not do it.

    Please help!!! ASAP
    Thanx

  45. Hi
    I already have a multiple cascading drop down set.
    I need a second non dependant set from the first but cant seem to get it to work....is it possible to have two independence, dependant cascading sets?
    Set A of drop down relates to cats, Set B relates to dogs

  46. Can you create an indirect function where if you have a list with three items, say, "fruits", "trees" and "other". You want the dropdown list to appear only for "fruits" and "trees". However, once you choose "other" from the list, I want to be able to enter my own text.

    Is this possible? I need a formula or another method rather than indirect function. Please assist.

  47. This tutorial helped me a lot... Thank you!

    I have an issue regarding cascading excel dropdown lists that I'm looking forward to solve it as soon as possible...

    It is the following,

    I created a cascading dropdown list with several dependent entries.
    Therefore, I developed a macro to clear contents when updating my ‘mother’ cell. The thing is, I did not want a macro to clear contents, but one to update them, giving me a value instead of a blank cell. I would like to keep the dropdown option in the case I want to change my standard value.

    I’m looking forward for someone how could explain it.

    Kind Regards,
    Fábio Pereira
    Portugal

    1. Hi Fabio,

      I would like to use a macro like the one you have created. So if I change a selection in a parent menu, I want the following menus to clear.

      Could you advise me how you did this ?

      Many thanks

      Chris

  48. Thanks for the great tutorial.

    One additional function i need to know.
    First column will be DropDown List and second column just shows the specific date.

    Example.

    We have 5 items names

    A
    B
    C
    D
    E

    And Values as follow

    60
    70
    80
    90
    100

    So I want in the first column A DropDown List which should contains a list of (A,B,C,D,E) and When I select any one of them it should show the related value (A=60) (B=70) etc

    Thank You

  49. Thank you Svetlana, I found this very helpful. I was trying it with another posting, but he didn't mention the use of a single word for the titles. Once I changed it - Excel was happy.

  50. Hi,
    I want to know if it is possible to put different lists in the same dropdown.

    Thanks,
    Alex

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)