Comments on: Set up dependent drop-down list for multiple rows in Excel

The introduction of dynamic arrays has made our lives as Excel users a lot easier. Among many other things, they significantly simplify the creation of dynamic drop down lists that you can copy across multiple rows. Continue reading →

Comments page 3. Total comments: 140

  1. Mark Seely says:
    March 6, 2021 at 1:27 am

    Hi Alexander,

    The article you reference is one that shows how to do multiple dependant drop downs for one row, and this article shows you how to create one dependant drop down for multiple rows. Now, how do create multiple dependant drop downs for multiple rows?

    I have the same problem is there a solution. I need to create multiple dependent drop down lists for multiple rows - possible surely.

    Exellent articles btw.

    Regards,

    Ivor

  2. Hi!

    Great tutorial! I am now trying to add another dependent column to this. Any suggestions on how to proceed?

    Thanks!

  3. hi guys, thank you very much for this, hoping you can help me to expand on the concept please?

    what i'm looking for is multiple dependent drop downs with a look up (i'll comma separate here just to show values):

    region, tower, practice, role, rate
    au, tower1, practice1, senior role, $100
    au, tower1, practice1, mid role $75
    au, tower1, practice1, role $60

    au, tower1, practice2, senior role, $100
    au, tower1, practice2, mid role $75
    au, tower1, practice2, role $60

    au, tower2, practice1, senior role, $100
    au, tower2, practice1, mid role $75
    au, tower1, practice1, role $60

    au, tower2, practice2, senior role, $100
    au, tower2, practice2, mid role $75
    au, tower2, practice2, role $60

    us, tower1, practice1, senior role, $100
    us, tower1, practice1, mid role $75
    us, tower1, practice1, role $60

    us, tower1, practice2, senior role, $100
    us, tower1, practice2, mid role $75
    us, tower1, practice2, role $60

    us, tower2, practice1, senior role, $100
    us, tower2, practice1, mid role $75
    us, tower1, practice1, role $60

    us, tower2, practice2, senior role, $100
    us, tower2, practice2, mid role $75
    us, tower2, practice2, role $60

    thanks in advance!

  4. Yeah my explanation of adding a none in the second drop down, obviously made no sense ?. But I have sorted it now by adding one none row in the source data and then in the preparation table I put this: =IFERROR(SORT(FILTER(Sheet1[[Name]:[Name]], ((Sheet1[[Type]:[Type]]=AA$1)+(Sheet1[[Type]:[Type]]=" None")))),"")

  5. This is exactly what I need. But in my excel version, the functions Filter and unique are not working. any workaround for this, please?

  6. Hi,

    I have my source data and preparation data on a different sheet, I've read the tip at the top about how to get this to work with that, but I can't for the life of me figure out what I've done wrong?

    I have my equivalent data like this:
    Data sheet called Adresseliste:
    Dept in column A2:A78 (Named Postnummer)
    Preparation data in i D:CB (varying row length, data starts from row 2)

    Sheet with dropdown I call my data like this:
    C2 Data validation with column with a list =Postnummer
    D3 should then be dependent on C2 for the options I can pick: =INDIREKTE("Adresseliste!"&ADRESSE(2; KOLONNE(Adresseliste!C2) + SAMMENLIGN(C2; Adresseliste!$D$1#; 0); 4) & "#")

    https://1drv.ms/x/s!AlfA0KbCw_8kg9IpkGxrYDXxVAMUhA?e=fTud37

    Apologize for the danish, but I've added a workbook as well.

    1. I realized that when you're working with excel in a different language, the sheet referencing can be a bit different.

      In my case, Spanish, I had to reference my sheet as 'MASTER DATA'! so I ended up with "'MASTER DATA'!"

      My formula in Spanish:
      =INDIRECTO("'MASTER DATA'!"&DIRECCION(83,COLUMNA(A1)+COINCIDIR(H43,'MASTER DATA'!B82#,0),4)&"#")

      I don't know in danish but you could try changing it.

  7. hi,

    I have a different query, What if i want multiple secondary drop-down based on one primary drop-down input.

    For EG. from the first (primary) drop-down, we select a country. Then other drop down lists Languages, Cities, rivers, etc should be visible for only that country in three different cells. So defining multiple ranges with the same name (country).

  8. Thank you for the replay but no sorry that’s not what I was trying,I don’t think I have explained it very well.

    What I want is to have a “none” for every item in the first column of the source data. Eg
    Dept. Manager
    Analysis None
    Project. None
    Planning None
    Design None
    Testing None
    Analysis Noah
    Project. Ava
    Planning Noah
    Design Sarah
    Testing Noah

    But I don’t want to have to add a “none” to the source data every time I add a new dept

    I tried to add a row of “None” to the preparation table (below the first transposed row and above the iferror formula) but the indirect formula won’t pick up this data.

    I have also tried adding a “none” in the validation box with the indirect formula and I have tried playing around to the formulas in the sheet. The most I can achieve is to add none to the end of every word in the drop down box and not as an extra drop down data.

    I hope that’s a clearer explanation.

    1. Hello!
      The drop-down list and any Excel formula write the value only to the cell in which they are located. For two cells, you need to use VBA.

  9. This has worked brilliantly.

    Any chance you can add an extra data to the second drop down? So that the drop down list includes in the list “none”.

    Example new dept, the list would say, Neal, Peter, None

    I have tried adding a row to the preparation table but it won’t let me reference that row.

    1. Hello!
      You need to add a record to the Source Data table. In the first column - New Dept, in the second column - Neal, Peter, None.
      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

  10. Hello
    I tried the same formula as mentioned in article. My problem is I cannot use # to get spill range. I can only get the top data for dependent list. If I put & "#" at the end, I always get "#REF!" error.
    =INDIRECT(ADDRESS(3,COLUMN(A2)+MATCH(G15,Cost,0),4)&"#")

    In my scenario, I have to use Cost (name for the title). I cannot use $B$2# to get the array range.

    How to use # to get the spill range please?

  11. Hi, this is a magical solution. Thank you.

    I adjusted the formula to make the list expandable and it seems to work. See below. Is there a reason you didn't do it this way? Will is cause me trouble in the future? The sheet is dedicated to this data so there is no issue with any data going in that shouldn't be there.

    =FILTER($D:$D,$C:$C=H1)

    1. Hi Sarah Clare,

      Dragging this formula to the right through more columns than there are currently entries in the header row will produce a few columns with zero values in your preparation table. A zero will also appear in your primary dropdown. To prevent this from happening, you need to filter out all blanks as explained in the "How to make multiple drop-down list expandable" example.

  12. Thank you Svetlana. This was extremely helpful. I was struggling with this for many days. It is solved now.
    One more help required. Your formula works only if preparation table is created in the same sheet where the drop downs is required. Can it be linked to some other sheet. ( The challenge I am facing is that the moment I insert a column, I have to again fix the formula in preparation table. If it is some other hidden protected tab, things would be easier.)

    1. Hi Monica,

      In "Step 3. Create a dependent drop-down list", there is a tip explaining how to adjust the formula for a preparation table on another sheet. Please check it out.

  13. Thank you Svetlana - this was a big help!

  14. This walk-through is great, the outcomes are magical. Thank you.

  15. Hi
    Is it possible to use a name with a "&" in a drop down list?
    My first list has two such names: "FOOD & DRINKS" and "SHOES & CLOTHING".
    I have found a way to deal with spaces for the 2nd drop downlist referring to the first: =INDIRECT(SUBSTITUTE(C274," ","_")) and when i look in the name list, the categories with & are named FOOD___DRINKS (with 3 lowers in between) so was wondering if it could be solved the same or similar way.
    =INDIRECT(SUBSTITUTE(C274," ","_","___")) does not work.
    Thanks!

  16. For some odd reason, I am struggling to create the dependency drop-down list. Do you have youtube videos?

    1. Hi Bavu,

      Nope. This solution is based on quite a complex formula for the dependent list, which you can copy from this tutorial, and then adjust references for your data set.

  17. Hi,
    excellent solution.... is there a way to get an advice (or a conditional formatting) in case someone changes the Dept after having filled the Manager? I mean for example in [Expandable drop down (table)] if someone changes [Dept.] from "Planning" to "Design", to see that "Noah" is no longer valid.

    Thanks
    Marco

  18. Hi, I can't for the life of me figure out how to replicate this for my purposes.

    I have 4 columns of data that are dependent on one another. The values in column 4 is dependent on the value chosen from column 3, which is dependent on the value chosen from column 2, which is dependent on the value chosen from column 1. Would anyone be able to take a look at my file and let me know how I make this formula work for unique drop down dependent lists?

    https://docs.google.com/spreadsheets/d/10ZSDrPBm0tSeL20vZVF8odPht79A5M3KCk_uhYeISP0/edit?usp=sharing

    1. Hi there,
      Any solution on this?
      I really need a help with that.
      Thank in advance!

    2. Hi,

      Did yall figure out a solution to this? I am trying to do something similar.

      Thanks!

    3. Hi Kaya,

      The file is closed for editing (we can only view the sheet). Please share its editable copy with us (support@apps4gs.com) so we could help you out. Thanks.

  19. Do you have any videos that show these steps?

  20. Hi, is there a way to make this approach work for a 3rd cascading picklist as well?

      1. Hi Alexander,

        The article you reference is one that shows how to do multiple dependant drop downs for one row, and this article shows you how to create one dependant drop down for multiple rows. Now, how do create multiple dependant drop downs for multiple rows?

        My use case is a training log that will have hundreds of specific training courses to choose from so I want to have three pick lists to narrow down the potential courses for each entry (row): 1) Course Category (Filter/UNIQUE), 2) Course Subject (dependant on course category), and 3) Course Title (dependant on course subject). How do I do that?

        1. Hi, I'm also looking for that kind of solution, combining multiple dependant drop dows for multiple rows. The two solutions are nice and easy to update and maintain. Combining the two methods would be great!
          I'm using an ugly solution, it's a pain to add a new row in source data, with multiples tables and formulas. Difficult to maintain.
          Thanks for your help if you update your article :)
          Kind regards

        2. I am trying to find the same thing as Mark above: combining both strategies into creating multiple dependant drop downs for multiple rows. In other words, replicate the functionality in the article Alexander referenced, but for multiple rows.

          1. Same here ... using the two articles and trying to combine the approaches simply doesn't work for me.

            Can you include a chapter that shows how to introduce a 3rd (and 4th, 5th, ...) column and still lets you create multiple dependant drop downs for multiple rows?

            Thanks!

            1. Hello Alexander,

              Have you figured out how to do this? Multiple drop-down levels with multiple rows....
              (Or at least let us know its not possible?)

              Please and thank you!

  21. Hi,

    I am using another sheet for the source data but it comes up with an error for the second drop-down list in the data validation.
    I think it may be the reference to the cell that is before the column in the prep table

    This one =INDIRECT(CHAR(CODE("E")...
    Is there a way to reference the other sheet in this setup?

    The full formula is: =INDIRECT(CHAR(CODE("E")+MATCH(E2,sourcedata!$F$2#,0))&"3#")

    Kind Regards
    Torkild

    1. For anyone else with this issue, simply change the source sheet name to Sheet1 and then copy the formula provided.

      Once the formula is working correctly, you can change the name of the sheet back to whatever you please and the formula will be updated correctly.

    2. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

      1. Thanks for the quick response and resolution to my problem.
        5 Stars rating all round! *****

        Kind Regards
        Torkild

      2. Thanks, Alexander,
        Just sent the email with attachments now.

        Kind Regards
        Torkild

  22. Thanks for this tutorial. It is incredible.
    One question...
    When inserting this formula into my DATA VALIDATION for a dependent dropdown list:

    INDIRECT(CHAR(CODE("col_letter") + MATCH(dropdown_cell, dropdown_spill_range, 0)) & "row_num#")

    ...I realize that it does not work ("results in an error) if the "col_letter" I am using is multiple letters. (in my case, the column preceding my data I need for the drop down list is in Column "BI". But sense the CODE function only returns the code for the first value...it is not working.

    Any way to make this work in a DOUBLE LETTER column?

    1. Hi Jonathan,

      Thank you for your question! I totally forgot about multi-letter columns, my bad. We have updated the formula and it now works fine for two-letter and even three-letter columns. Please see the "How this formula works" section for the detailed explanation.

    2. Hello!
      Without seeing your data it is difficult to give you any advice.
      If I understand your task correctly, the following formula should work for you:

      =INDIRECT(ADDRESS("row_num#",COLUMN(INDIRECT("col_letter"&"1")) + MATCH(dropdown_cell, dropdown_spill_range, 0)))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  23. Svetlana, I see you are very proficient in EXCEL. I have a complicated formula that I need help with and can not figure out how to create it. Would you be willing to assist?

    In Cell C29 there is a percentage (25%) automatically calculated. If that percentage is less than 25% then a number needs to be added into cell C5 to make C29 reach the required percentage of 25%. I have tried everything possible and can not seem to figure this out. Would you be able to assist? Greatly appreciated.

    1. Hello!
      What formula do you use to calculate the percentage in C29? If a number is already written in C5, then you can change it either manually or using a VBA macro. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

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