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 9. Total comments: 331

  1. It was a great article it really helped me alot.Thanks alot for you help. I really appreciate your time and help. Thanks again :-) buddy

  2. I just want to say thank you for your generosity. This tips will make me perform better at work. I made a search for this in the internet for a while, and your website was the only one to post the exact answer.

  3. Hi

    Great post, very helpful. However, each of the list in my table is mixed, it might look like this;
    Apricot
    Apricot Italy
    Apple Morocco
    Mango China
    Chile Apricot

    Rather than separate out the individual lists, how can I make the 2nd drop down list only show those values that word match the 1st drop down, i.e Apricot Italy and Chile Apricot?

    Probably not the most elegantly written post.

    Cheers

    1. Hello, Paul,

      Sorry, it's not possible the way you describe. You should add another column and filter it using the formula.

  4. I have 2 columns in a spreadsheet, both are picked from a drop down list.

    Col A is "Do you want more information" - Possible Values Yes\No

    Col B is "How did you hear about us" - Possible values are Email\Phone\Other

    Question: If "No" is chosen in Col A, then Col B's value should be N/A, otherwise the user should be able to choose from the dropdown and pick a value. Using the following formula, I can get N/A to appear, but the "false" option overwrites any available dropdown values or previously selected values. So simply put, if Yes is chosen, let user choose a value in the other cell. If No is chosen, show N/A in the other cell. Thoughts on how I can get this to work? or how it's work with data validation?

  5. Thanks for the tutorial.
    Help me a lot with my problem.

  6. Sir,

    Please help me to get dropdown list based on the followings:
    I have a list of names, how to create dropdown list to populate the list of the words to display when the first, then second letter (and so on) of the word typed.

  7. Good day,

    Thank you soo much for the assistance. I have managed to create a multiple word cascading drop down list. The problem is that the result on the dependent list only references to whatever is on the first row of the main drop down list. How can i correct this?

  8. I have one question.

    I have Banks
    Abank
    Bbank
    Cbank

    And Eachbank has same currency
    USD
    GBP
    EUR
    CHF
    JPY

    And Each Currency has some special Number
    Abank USD SPECIAL NO 111
    Bbank USD SPECIAL N0 112
    Cbank USD SPECIAL NO 113
    same for EUR,CHF,JPY,GBP

    I this stiuation

    When i selected First Dropdown i wanted to show Banks-

    Second dropdown wanted to show All currencies-

    Third dropdown wanted to show Special no

    Let me give you and example
    IF Abank selected on DRPList1
    and USD selected on DRPList2
    show only ABANK USD SPECIAL NO 111 on DRPList3

    IF Bbank selected on DRPList1
    and USD selected on DRPList2
    show only BBANK USD SPECIAL NO 111 on DRPList3

    But it is always showing ABANK USD SPECIAL NO 111 DRPList3

    So how can i connect lists if my banks has same currencies and this each same currencies has different special number

    Thanks

  9. Perfecto

  10. This is an wonderful illustration, thanks! But I'm getting stuck in a problem. My requirement is to create a task and sub-task list and populate that on daily basis. I mean master sheet will contain tasks like Functional Design, Technical Design etc.; sub-tasks like preparation, review, rework etc. There will data validation in user input sheet where user will select a task on daily basis and a sub-task will be populated accordingly. Above example is working only for a particular row. Because "fruit" refers to cell "Sheet2!$B$1". What do I need to do to make it applicable for Sheet2!$B$2..$B$10 and so on?

  11. I would like to make a drop down from a table where i select an item # (column A) and the description (column b) automatically appears. Is this possible?

  12. article is very good but i have one query..

    in C column i want to show some data dependent on what select in B column. i know by using indirect function we can do that. but how to implement this logic to entire column? can you please help becuase it is very hard to select each cell update indirect(b2), indirect(b3)...etc...

  13. Not sure if I understood it

  14. It's really weird. I've done exactly what you suggested, but it's not updating automatically - I wonder what I'm doing wrong. I'm using Excel 2013.

  15. Hi all,

    How can add a Validation Button On My Sheet. How can add the sheet Which is in Drop Down List

  16. I want to select data from three drop down list ddl1, ddl2 & ddl3 and the result should be shown by matching the details.

    For example: I have ddl city1 & city2 and third ddl as mode_of_transport.

    When I select a city from city1 and another city from city2 and from third I select mode as ROAD, then the result should display cost of ticket and time taken.

    Is it possible ??

  17. Hello,
    I have the 2 dropdowns. The first se;ects a type of machine, once selected the second will display only the material that works on the machine. When selecting another machine in the first dropdown I would like the second that displays the material to go blank and ready for a selection. Reason is by not going blank the material that is still listed will be associated with the first machine selection making a error. By going blank with a new machine machine is selected in the first dropdown will force the person making the selection to select the correct material thus eleminating the chance of the error.

  18. Great Tutorial Thank you!

    I have 2 worksheets. One has 14 Columns. Each Column has between 2 and 8 items to select. On the next Sheet I have a drop arrow for one cell to choose one of the 14 column items (thanks to your tutorial) However, the next cell needs to give me the items for each column header. I have tried data validation, list, and since my source is on another sheet: =Indirect(SUBSTITUTE(Sheet2!$A$1:$D$7))I then get an error message. Is this a 2 steps process somewhere, or what am I missing?

    Thank you,

    Erin

  19. Hello,
    I followed your article closely and have managed to achieve what I intended. Thanks a ton.
    However, I am unable to apply it to other cells (rows below) if i drag the bottom right of the cell where the formulae are working well.
    What am I doing wrong?
    Thanks in advance,
    Shreeram

  20. I after something similar. My drop down list is either BPay or Bank Deposit. If they select BPay, I want Biller code and Reference to appear in 2 different cells below. Like wise, if Bank Deposit is selected, I want Acc No, Acc Name, Bank Name and BSB to appear in 4 cells below.

    Is this possible?

    Thank you
    Dan

  21. Hi there!

    I have been looking for a way where by if i enter an item's name ie. Bag, in the next cell the price ie. $19.50 would automatically appear. Is it possible on excel?:)

    Thank you!
    -Sarah

  22. Svetlana,

    So I have two columns, each being a drop-down list. I want to be able to change either list, based on the choice in the other cell.

    For example, if i choose "apple" from the drop down list in A1, B1 will then read "red".
    But conversely, i would like to choose "pink" from the drop down list in B1, and have it change A1 to "peach".

    And this applies back and forth so that each column, A and B will be updated no matter which list is chosen from, all the while keeping them drop down lists.

    Let me know if you can help! That'd be amazing

    -Sam

  23. Hi

    This helped a lot. I created a dynamic drop down list referencing another drop down list, following your steps and it work perfectly.

    My problem is now when I try to copy those cells and defined names to a second sheet to have different information shown on sheet2, the defined names still reference the first sheet only. Is there a way to have the entire name (in your case the 'fruit' name) reference sheet2!A5, sheet3!A5, etc? Everything is the same as sheet 1, I just need the fruit cell to reference the new sheet instead of the old.

    1. I am facing the same issue, @Nathan, did it resolve for you?

  24. Hello,

    What if your dependent variable on the table is a number.
    I am having this issue. Excel says that the dependent name can't be a number, and I need my 3rd column to be dependent on the 2nd column (which is composed by numbers only).

    Thanks

  25. Is it possible to create sub-choices within a drop down list?

    Apple
    - green
    - red
    Mango
    - for juice
    - for pulp
    - fresh
    - tinned

    Similar to the menu > sub-menu > sub-sub menu > sub-sub-sub menu cascade that you have with Windows "All Programs"

  26. Great tutorial! I am trying to apply this dependent dropdown list to an entire column. Eg. What is selected in B2 will affect the dropdown menu in C2. What is selected in B3 will affect the dropdown menu in C3. How do I apply this rule to the entire column (of 20,000 lines)??

    When I select the whole column to apply this rule to, C3's menu depends on B2 instead of B3.

    Please help!

    1. I'm having the same issue, did you ever figure out how to remedy this? Thanks!

  27. Hi, great article!
    I've also read the article "Creating a drop down list in Excel: static, dynamic, from another workbook" and I learned a lot!
    Right now I'm trying to make a 3 level cascading list, Country, Province/State, City/Municipality, that is editable in the future.
    I would like to know if its possible in excel.
    If possible, sending me a template would help a lot, or at least please tell me how to.
    Thanks a lot in advance!

    1. Hello JIrman,

      Please check out "5. Add a third dependent drop-down list (optional)" in this tutorial. It explains the detailed steps on adding the 3rd level.

  28. Hi, great article!
    I've also read the article "Creating a drop down list in Excel: static, dynamic, from another workbook" and I learned a lot!
    Right now I'm trying to make a 3 level cascading list, Country, Province/State, City/Municipality, that is editable in the future.
    I would like to know if its possible in excel.
    If possible, sending me a template would help a lot, or at least please tell me how to.
    Thanks a lot in advance!

  29. hi,

    I want to allow my user to start typing in the cell where we have applied drop down and then the drop down to show only values matching his entry. As my list is a big list which makes it difficult to scroll down n select.

  30. Hello,

    I am trying to create a pricing list using 2 text drop-down lists.

    Column A "Install" + Column B "Red Cabinet" = $2,500.00 (from a pre-made list)
    Column A "Move" + Column B "Blue Cabinet" = $2,000.00 (from a pre-made list)
    Column A "Remove" + Column B "Green Cabinet" = $500.00 (from a pre-made list)

    Any help you could provide would be greatly appreciated.

    Thanks!

    Lee

  31. Hello,

    I love your tutorials, very useful and easy to fallow. I wanted to enquire if you done a tutorial on how to create dynamic cascading drop-down lists with three columns (where column three depends on column one and 2).

    Thank you

    1. Hello Judita,

      Thank you so much for your kind feedback. Please check out step 5 "Add a third dependent drop-down list (optional)" in the first solution in this tutorial.

      1. Hi Svetlana,

        This INDIRECT doesnt work when Table method is used to create dynamic lists. Please provide a tutorial for when this method is used.

  32. HI,

    Very good article. Thanks for this.
    But is there a way where after having this list, I will have one more cell where I enter some info. All these details along with dropdown selection and the cell info come together in the final cell separated with semicolon ;
    Ex: In below ex A1 to A3 are drop downs and A5 is text box where I can manually enter details also A6 is text box where all details are collated and put up and not editable
    A1 : Health
    A2 : Fruits
    A3 : Apple

    A5 : Apple is good for health

    A6 : A1:Health ; A2:Fruits ; A3:Apple ; A5: Apple is good for health

    1. Got it..it was simple...

  33. Is there a way to use drop down lists to entirely different sets of data? E.g. I have a list of managers and their employees for a "progress tracker"; is there a way I can select one manager from the drop down, their list of employees and all the related data for those employees will be displayed? Maybe using a drop down is not even the proper fuction for this?

    Thank you!

  34. You rocks!!

    Well articulated and understandable. I have navigated plenty of google search results but its found to be very simple to understand and implement.

    Thanks much

  35. Good explanation on dynamic dropdown list and additional points to trace the error when mapping the dependent list to maindropdown list.

    I was reffering many sites on this indirect function. None was pointing out to the formula index, prior to the final step indirect function. It was throwing error after possible all tries (Cell Reference Error).

    Many Thanks for this post:)

  36. Hi

    my question is how to create main dropdown option ex i created dorp down option where ever i check a)jack b) tom c) harry in 500 cells if i want to know total numbers of onyone like how many tom are there then what to do ?

    Need your help

  37. Is it possible to create a dependent dropdown list based on two columns?
    For example:

    Col A
    "Proc 1"
    "Proc 2"
    "Proc 3"
    "Proc 4"

    Col B
    "1.1 Sub-Proc"
    "1.2 Sub-Proc"
    "1.3 Sub-Proc"
    "1.4 Sub-Proc"
    "1.5 Sub-Proc"
    "2.1 Sub-Proc"
    "2.2 Sub-Proc"
    "2.3 Sub-Proc"
    "3.1 Sub-Proc"
    "3.2 Sub-Proc"
    "3.3 Sub-Proc"
    "3.4 Sub-Proc"
    "4.1 Sub-Proc"
    "4.2 Sub-Proc"

    I would like to create a 2nd dropdown list based on the first Char of the Column B dependent on the last Char of a result of a 1st dropdown list based on the Column A.

    As a Result, if I select "Proc 2" on the 1st dropdown list, I would like to have the following Sub-Procs listed on the 2nd dropdown list:
    "2.1 Sub-Proc"
    "2.2 Sub-Proc"
    "2.3 Sub-Proc"

    Tks in advance
    LCoelho

    1. No, you would have to have your main column:
      Procedure 1
      p...2
      p...3
      then your headers would have to be
      p...1, p...2, p...3 etc..

      and your sub proc. would have to be under their respective col.

  38. Hi, I've been looking on the internet for a while now trying to find what I am looking for. The problem is I don't know what its called. I know hot to do the drop down menu (data validation) which is easy and fine however, what I want is for when I select an option from my drop down menu, some other cells to be automatically populated with data.

    I.e.

    Pressure Torque
    4.0 BarG 20Nm
    5.0 BarG 40Nm

    So when I select 4.0BarG from the drop down menu, I want another cell to be automatically populated with 20Nm. Is this possible? If so can someone point me in the right direction please.

    Hopefully someone can help/advise me what this function is called so I can search better.

    Thanks!

    1. Use IF statements. It will take a while to figure out but just use the logic.
      IF a cell equals another cell then make the cell with the IF statement return the value if true.

    2. Post 31 that Michael entered is exactly what I am trying to figure out too. Any help would be much appreciated.
      I would like to create a spreadsheet with one drop down list that then populates 2 other cells.

      I.e.

      ColumnA ColumnB ColumnC

      Company name Currency Commission

      So you select a company name from the drop down list and it automatically fills out the currency type and the commission percentage for that particular company for you.

      Is that possible?

      Many thanks in advance,

      James

  39. Hello,

    I have made a drop down menu in the one column.
    Now I also want to setup such a way that if I choose one entry from drop down menu in column A then I should get value in column B corresponding to my choice in Column A.

    Thanks

  40. I have a column with a heading and values below. the heading has a drop down with two selections. I want that when I select on or the other that the values below change. For eg. if i have a different figure for budget and target I would have budget and target in the drop down and the figures below would change depending on which one I selected.

  41. How to assign same value to a cell using dependent list?
    For(your)example: how can I assign/populate same exporters for two fruits say 'Mango' and 'Apricot' dynamically without duplicating the values?
    Please let me know.

  42. Hi,

    could you tell me how to make cascading (dependent) Excel drop down list where entries repeat, but I only want it be mentioned once in the drop down list? E.g. first row - Supplier Tesco Tesco Tesco Argos Iceland Iceland. I the drop down list I want to see Tesco Argos Iceland.

    Thanks!

  43. Hey Hi!

    Thanks for this extremely helpful article.

    I have a small error in implementing it, can you help me in solving it?
    I created a original list, and a dependent list.

    However, the dependent list accepts value only if the 1st option of the original list.
    If I select any other option other than the 1st, then the dependent list doesnt work.

    Can you let me know how to rectify it?

  44. Is there a way to copy info in a cell - example: ADM-90-13 - without having Excel automatically change the "13" to 14, 15, 16, etc. as it populates the next cell. I've been cutting and pasting and it takes quite a while to accomplish this.

    Thanks for any help you can provide.

  45. I want know. If i made a drop down list containing names of fruits and i want a specific number to appear in another cell depending on the selected fruit. How can i make that ?
    Thanks in advance.

  46. Hi Svetlana,

    I am trying to create something similar to your example above. But I am using a number drop down of Values 0-10. Every time I try to create the third dependent drop down, based on the previous two drop downs, it won't create in Name Manager as it says the value I am combining collates to a field in the spreadsheet (Win1).
    I can do everything I need by putting the values of Zero-Ten in text format but is it possible that the values are based on Zero-Ten but the user selecting shows the Number value 0-10

    Thank you

    Mike

  47. your work is helping me a lot. i would like to know that if i select a certain option from drop down and fill some data accordingly then if i change the drop down selection then the data should change accordingly.how do i do it?

  48. Thank you.

  49. I want to create drop down menus for a consumer to choose from (I have them created). Once they select what they want I want to be able to gather and list information in a new sheet that is gathered from multiple different sheets within the same project. I want to basically write an if/then statement that if they select this from the drop down list and something from a different drop down list then a master list appears with what they need. It's kind of a generic example but anything helps.

  50. @Eric remove the "$" from the dynamic location of the cell. Refer to Karen's comment (third from top). She faced same issue for columns and removed $ from the column letter. For rows, keep the $ for row letter but remove it from row number

    So your reference to the first cell would become from
    = Sheet2!$B$1

    to
    = Sheet2!$B1

    I have tried this and it works a treat!!

    1. I am also facing the same problem.
      @Fawwad removing the "$"from the dynamic location of the cell is not working. Please advise if any alternate solution. Thanks !

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 :)