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

  1. 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?

  2. 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?

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

  4. Not sure if I understood it

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

  6. Hi all,

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

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

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

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

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

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

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

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

  14. 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?

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

  16. 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"

  17. 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!

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

  19. 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!

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

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

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

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

  24. 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!

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

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

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

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

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

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

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

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

  33. 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!

  34. 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?

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

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

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

  38. 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?

  39. Thank you.

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

  41. @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 !

  42. Using your instructions above, I’ve been able to create my dynamic, cascading validation successfully. Then I copied the validation into subsequent rows. My problem is that when I’ve selected from the dropdowns in the first row, the dependent dropdowns for subsequent rows are stuck matching the first row. Can you tell me what I’m doing wrong? Thanks!

  43. Is there a way to automatically update the exporter cell to the first item in the list once the fruit is selected?

  44. In a drop down list how to select a name starting from letter 'T" quickly a particular name from a huge drop down list.

  45. Hi,
    Thank you for a good description, but i have a question, how can i use this multilevel drop-down list not for one cell, do it for more than 100 cells at once.
    I want to make a list for 100 product, and ask people to fill other fields like that you teach. but I cant do it for 100 products one by one. Help me !!

    1. I am having same problem as Saleh, how can we fill the multilevel drop-down so it can work for hundreds or thousands rows and not be dependent on what the 1st cell's selection is? Right now when you fill the row it only brings the drop down list from the 1st selected cell and building this formula row by row can't be the only way to do this repeatedly is it? HELP!!

  46. how can i return a certain data using VLOOKUP??? for example using just an id number i have to return the name of that id number user? is that possible in data validation???

  47. Hi,

    Is there a way to create a single or multiple substitution teachers table with
    alternative pop-up options in the time-table.

    1. Hello Sangita,

      Please specify what you mean by "alternative pop-up options".

  48. Is it possible to exclude blank rows which appear through the data list for example in your data table if A2 was blank, currently this would appear as a gap in the drop down list which I wanted to avoid.

    Thanks

    1. Put in your data validation list instead of exporters_list this string:
      =OFFSET(exporters_list;0;0;COUNTA(exporters_list);1)

    2. Hello Matt,

      Regrettably, there is no quick and simple way to fulfill this task. You'll definitely need to create a temporary table and copy all non-blank rows there.

  49. Is it possible to apply this to multi select drop downs? So, I would select Apricots and Mango (using a VBA script) and then see the related suppliers to both Apricots and Mango?

    Thanks a lot!

  50. Svetlana,
    I am working on a spreadsheet and I was wondering if it is possible to apply dynamic cascading lists to 3rd or 4th dependent lists. Is this possible, and if so, how would I go about doing it?

    1. I am looking for this sort of thing too.

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