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

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

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

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

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

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

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

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

  8. Thank you.

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

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

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

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

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

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

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

  16. Hi,

    Instead of single value / item, i wish to display some bullet points for 2nd dependent drop down against selection in the first dropdown.

    Please reply ASAP.

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

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

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

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

  21. Svetlana - I have got the drop down box process down, and have been able to make them dependent. My question is: Is there a way for me to make this applicable to an entire column, without manually changing the data validation in each cell. Example being, I want column C drop boxes (from row 2 to 10,000) to be dependent on column B drop boxes, ie drop box cell C25 would be dependent on drop box in cell B25. Right now I can only get everything in column C to tie to B2...

    Thanks!

    1. Yes,
      Using the example I added columns going across so the change for you will be slightly different.
      The key is the Name "col_num" in the example. It uses the =MATCH(fruit,fruit_list,0) which references the named cell "fruit". This needs to be changed to reference the cell relative to the current cell (The cell where the second list is being used.
      I changed this to use the INDIRECT function to reference the cell one row up so my full function used in the "col_num" name is: =MATCH(INDIRECT("R[-1]C[0]",FALSE),fruit_list,0)

      The "R[-1]C[0]" is the relative reference looking at the value in the cell one row up in the same column. If you have your selections going down in two columns then your relative reference in the INDIRECT will look one cell to the left in the same row or "R[0]C[-1]"

      1. Hi,
        I followed thoroughly your instructions and they work just great on my desktop Excel. However it doesn't work on the Excel iPad version (also not in Excel online). Whereas the static/fixed reference (ie "=MATCH(fruit,fruit_list,0)") works both on desktop as well as on iPad. Any ideas it isn't working with the INDIRECT function?
        Would there be any other ways to reference a cell relatively to the current one?
        Thanks!

        1. Hi Raimonds,

          Unfortunately, we can’t say for sure why the solution doesn’t work on the Excel iPad (in Excel online) and can’t suggest an alternative. The point is that the functions are very limited in Excel for iPad and Excel online. Microsoft is constantly trying to improve both versions, but it is still not possible to fulfill some complex tasks there.

  22. Hello guys,
    question related to - dynamic cascading drop-down lists
    I followed the steps, done = working for the first cell (B1 in your example)

    How you copy it to the entire column? I tried normal drag (select the corner bottom-right and drag below) and I destroyed the results in the 2nd column ....same thing happened when I did it in your sample
    Since I need hundreds of rows daily, doing B1 procedure in all manually is not possible
    Thanks for the great help you provide so far

  23. I was Struggling for dependent list -- how to create in excel /// After 1 and half year ... I got this from here .............!!!
    O My Good.

    Thank YOu very very very Much ......... :)

  24. Hi,
    I have made three dynamic named ranges using offset formula. All these three lists are on different worksheets. I want to make a dependent dynamic dropdown on a 4th sheet. is it possible ? and if yes, how?

    for ex: store1, store2, store3 these are three named ranges on sheet a , b, c respectively. now i want to make a dependent dropdown in which first dropdown should have store1, store2 , store 3 and the second dropdown must have the items listed in these.

  25. Hi,

    Instead of preventing changes to a cell once a selection has been made; what if we wanted all the responses to be cleared when a user has made a selection for 1, 2, 3 etc. and then decides to go back to 1 to change the response.

    For instance, I've got three drop down lists in C1, C2 & C3 and then want C2 & C3 reset to blank, when I change C1; or maybe C3 to blank, when I change C2. The point is to reset the responses provided after the new target cell. Is there anyway to achieve this?

    Thanks a lot in advance.

    1. I am looking drop down list and if I go to list of drop down and select one. I need another drop down from that drop down list. Can it will be possible

    2. Try using macros: Right-click the sheet and select view code. Copy and paste the below text. Enable macros.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$C$1" Then
      Worksheets("Sheet1").Range("C2,C3").ClearContents
      End If
      If Target.Address = "$C$2" Then
      Worksheets("Sheet1").Range("C3").ClearContents
      End If
      End Sub

      1. Hi, If I have named my sheets - ie I've called mine Analysis so I just substitute "Sheet1" with "Analysis" or do I need to keep the sheet number?

        Also how do I actually get this to work - do I need to run it? or save it as a macro name?

        Many thanks
        Marcia

      2. How would you alter the code below if I have more than one lkinked data validations on one worksheet?

        For example I have the same situation as below but in columns D, E, F, etc.?

        Thanks

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$1" Then
        Worksheets("Sheet1").Range("C2,C3").ClearContents
        End If
        If Target.Address = "$C$2" Then
        Worksheets("Sheet1").Range("C3").ClearContents
        End If
        End Sub

        1. I'm running into the same issues as Ron. I have 1000+ rows that have multiple dependent drop-down lists; how do I edit the code so that it applies to all rows?

  26. I tried this for my project and found I could not fill to the right to copy the data validation across.

    So for instance in your example I had entries at B1 and B2. I then entered another fruit at C1 (different to the fruit entered at B1). I found that B1's dependent drop down list was appearing in cell C2 instead of the dependent drop down list for C1 as I wanted.

    I fixed this by removing the absolute reference in Step 2.2 as follows
    = Sheet2!B$1 //ie no $ before the B
    I was then able to fill the data validation from B2 to the right, so that C2 was dependent on C1; D2 was dependent on D1 etc.

    I'm not sure if any of this made sense. I hope it has.

    I have to thank you very, very much for this instruction. It has been a great help.

    1. Nice! I also had this issue, since I am doing this in a form... So for "fruit" I named an interval like "=Sheet2!$B5"

      This way all the cells in the columns are now working well. Hey, Svetlana, maybe it would be worth it to add this to your tutorial, since many people seem to need it variable and not just for a single cell.

      Cheers guys!

    2. HI Karen
      How can these drop downs work in another sheets (of same workbook)

    3. Thank you! That was my problem too!

    4. Awesome! that was my issue, thanks!

  27. I need help with part of you tutorial.
    In creating a dynamic cascading drop down list in excel,
    under 2.3. Create a name to retrieve the dependent menu's entries.

    Which cell or range of cells gets =INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0)) as its name.

    Where do i place this name

    1. Hello Ferrol,

      This is a very good question. This name is not assigned to any particular cell or a range of cells. In fact, this is a named formula that retrieves the correct list from your table depending on which selection is made in the first dropdown.

      The main benefit of using this formula is that you don't have to create an individual name for each entry, one named formula covers them all.

      You create this name in the same way as the two previous ones (steps 2.1 and 2.2) by clicking Formulas > Name Manager > New, or by pressing Ctrl + F3 and then clicking New.

      1. Dear Svetlana,
        I tried Step 2.3 - and it keeps throwing me error.
        In Name Manager - the Refers To section keeps on putting quote around INDEX ie. ="Index(...)"
        If I delete the quote, it says There's a problem with this formula and I cannot save it.
        Hence the Value in Name Manager doesn't evaluate.
        I copied your example to the tee and I'm using Excel 2016
        Is there a solution for this?
        Many thanks.

  28. Hi

    Thanks for the example. My question is how do you prevent someone changing the fruit cell once the exporter cell is chosen?

      1. I was able to prevent changes in first drop-down after they select a choice in second thanks to the advice. But now, I can enter things off the drop-down menu. Did I inadvertently turn something on?

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