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 2. Total comments: 144

  1. Why does removing the $ to allow mixed cell reference not allow the second drop down to work? I edited your sample and it broke (named item fruit)

    Thanks
    Bill

  2. Thanks a lot very helpful... but not with figures. :)
    I want to create the same but with figures and percentages but it does not work. Is there any difference? Is it possible to do it in excel?
    Thanks

  3. I am running into difficulties when referencing the source data which is on another sheet called Master
    list: This works with all the data on the same page: (my test page)
    =INDIRECT(ADDRESS(2, COLUMN($K$1) + MATCH(I2, $L$1#, 0), 4) & "#")

    But when I try and add the data validation to a cell on another tab in the workbook it doesn't work: (note Match(G6 has been updated as that is my new drop down cell)

    =INDIRECT("Master list"&ADDRESS(2, COLUMN($K$1) + MATCH(G6, 'Master list'!L1#, 0), 4) & "#")

    Can you see where I maybe going wrong ?

  4. Hi,

    I want to create a dropdown list with non-unique values. I have 2 groups of candidates, Group 1 and Group 2.

    So each candidate is assigned to either group, and I want to be able to select names from that group but it seems like the examples I've come across cater to unique, non-repetitive values?

  5. Is there such a thing as the database of information in the Table being too big? I cannot seem to make this work on a large scale, but I did make it work on a much smaller scale. Just wondering.

  6. Hi Svetlana / team,

    "Creating dynamic cascading drop-down lists in Excel" - you perfectly described my use case!
    I was researching for three weeks how to use extract a data validation range from one table, organized into dropdown 1 (=headers) and dropdown 2 (=individual columns). I have to admit while using Excel for two decades, i never was proficient enough for creating more than basic sheets.
    VLOOKUP, HLOOKUP, INDEX+MATCH, using helper cells for storing extracted range references... ewww.
    Thank you so much for sorting this out for me, really appreciated, have a remaining nice week!

    Btw, additional info you might think about adding:
    - Excel 365 / Excel Online absolutely want semicolons instead of commas in the published formula: =INDEX(exporters_tbl;;MATCH(fruit;fruit_list;0))
    And i have observed this requirement at many other places with formulas too. Not all, though.
    - Excel 365 online version does not show empty rows as values, Excel 365 desktop does

    1. Hello!

      The use of semicolons / commas in Excel formulas is controlled by the List Separator, set in your Regional Settings. Comma is the default list separator in North America and some other countries. In European countries, comma is reserved for the decimal symbol and the list separator is usually set to semicolon.

  7. Hello,
    I've read through this article quite a few times trying to apply it to my project here but keep running into errors. There may be just too much data to do this within excel but maybe I'm just missing some step. Hopefully someone here will be able to guide me in the right direction.

    I've created a database consisting of multiple worksheets where each one represents 1 Year (2000, 2001, 2002... etc.), all the way up to 2021. Within each sheet, a table resides consisting of the same 28 Coloumns with Headers representing Car Manufacturers (BMW, Mercedes.... etc.). In the Rows under each Column Header are the Car Models per that Year.

    From this, I'm trying to make 3 dynamic dependent drop-down lists (Year, Car, Model), where the last drop-down list (Model), would be dependent on both previous selections (Year, Car).

    Is there a way to create this drop-down list to include and pull the source data from all the worksheets? So that I have just 1 set of drop-down lists?

  8. I have 2 Dropdown List with the same options (Included and Excluded) in different sheets. If I select " Included" in one dropdown list, it should also select "Included" on another dropdown list I need VBA code for this.

  9. Svetlana,

    Thanks for sharing this just in time!

    I am able to design my sheet!

  10. Hi I would like to create a dependent dropdown list but my data is in this manner and its huge data. I only give 2 deps as example but there is a lot of deps. How can i make a drop down with this where my data in class is dependent on the dep.
    Example:
    DEPS CLASS
    SHOES KIDS
    SHOES KIDS
    SHOES KIDS
    SHOES MEN
    SHOES MEN
    SHOES WOMEN
    SHOES WOMEN
    CLOTHING UNISEX
    CLOTHING UNISEX
    CLOTHING UNISEX
    CLOTHING MEN
    CLOTHING MEN
    CLOTHING WOMEN
    CLOTHING WOMEN
    Thank you so much if you can help on this.

    1. Hello Mary!
      I can’t create a dependent drop-down list for you, because I can’t work with your data. I can only help with advice. But for this, ask a specific question - what is the problem, where the error occurs, which formula does not work.

  11. I have tried to follow the instructions above but am getting an error when I put in the formula for the second drop down(The Source currently evaluates to an error. Do you want to continue?) When I chose a selection in the master list, four out of five selections get no response, but one selection (the second option) then fills in the second drop down with options to choose from.

    What may I have overlooked or need to modify in order to get the drop downs to work? Your assistance is greatly appreciated.

    Thank You

    1. I am trying to create a dropdown with options that will then change the option in the second dropdown. I have five options in the first dropdown. I at first used the =INDIRECT($B$24) and then changed to =INDIRECT(B24) and finally have used =INDIRECT(SUBSTITUTE(B24," ","")) as my options in the first drop down have spaces. As I mentioned in the first comment, when I enter the formula for the second dropdown it gives me an error message, The Source currently evaluates to an error. Do you want to continue?, and I am unable to see four out of five of the options in the second dropdown. The second option, which I have as Inventory, will show in the second dropdown but not any of the rest. I am trying to have a person choose one of the options in the first dropdown, which will then give them a second list to choose options from. Such as with my Inventory, then gives the additional options of (Click to Select), Initiate Rental Agreement, Develop Inventory Management Plan, Create a Par Level, Create a sign-out sheet, Other. Right now the first dropdown is in cell B24 and the second dropdown is B35 on Sheet 1 and my list are on Sheet 2.

      1. Hello Lin!
        Here is what I could understand. You have the first drop-down list in cell B24. It consists of 5 positions. The second drop-down list is on the same sheet in cell B35. It works with the formula = INDIRECT($B$24).
        Next you need to create named ranges. Their names exactly match the values from the first drop-down list. This is described in detail in the manual above. Read it carefully. If you do not, then the second drop-down list will not work.
        Perhaps this is the cause of the problem.

        1. No sir, it does not work with any of the formulas. Currently only the second option in my first drop-down will bring anything up in the second drop-down. I currently have the formula =INDIRECT(SUBSTITUTE(B24," ","") as my first drop-down consist of Infection Control, Inventory, Procedure or Protocol, Site or Facility Need, Staffing Shortage. As for the named ranges, yes I have created the named ranges to match the names with the space changed to an _, such as for Infection Control the named range is Infection_Control or for Procedure or Protocol as Procedure_or_Protocol.

  12. Hi, I am having an issue in indirect function. I have used name range method and the drop down works to pick up value from the the list. It works and drop-down list show correctist for first row. When I copy down the rows with fuction for entry, the drop-down does not give the correct referenced list and show list from one row above of the actual referenced cell.
    =INDIRECT($A$2). It should pick actually from row 3 which is like =INDIRECT($A$3)

    Grateful if anyone can help me on it.
    Thanks alot
    Michel

    1. Hi Michel,

      Using $ ties the indirect function to that cell alone
      instead Use =INDIRECT(A2) and you can then copy down for the next few rows with no issues.

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

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

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

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

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

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

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

  20. Hi Svetlana
    I thank you for your post, I found a different way to get to a solution to the cascading drop down boxes. My situation has four levels to select and I didn't want to make named ranges for all of the possibilities. If I'd found your post first I would have tried the option using the tables. My solution involves having a pair of columns of data for each level after the first. I used a pivot to generate each set of data as by base data is subject to change. I then use a match and countif combination to build an address range based on the previous selection.
    My method for dealing with a change at a higher selection level was to clear the selections to the right of the change using an on-update event.
    It's all a bit convoluted but it works. I'm happy to send it to you if you'd like to have a look. My description may be a bit brief to convey how it works.
    Ron

    1. Hi Ron,

      Thank you very much for sharing your solution. I think I got the general idea though not the details. The table option would probably be easier to implement but your approach is original and unique, at least I've not encountered it anywhere else. Excellent job!

  21. Hi. Thanks for the tutorial.

    How to make a third dependent dynamic drop down based on data selected in the second dependent drop down?

    1. I tried the one mentioned in "5. Add a third dependent drop-down list (optional)" but after using the Table method for the second drop down, this doesnt work

  22. Thank you for posting this tutorial. Using it, I was able to set up the dependent drop down lists I needed. Now I need to figure out how to make them work across multiple tabs. The problem is stemming from the initial cell reference. Here's my example:

    NAME: CELL_1_DOOR_TYPE
    FORMULA: ='1ST FLOOR'!$C$5

    It works perfectly on the 1ST FLOOR tab. The problem is, of course, that if you're on 2ND FLOOR, 3RD FLOOR, etc., it references C5 on the 1ST FLOOR tab.

    Is there a way to force the named formula to always reference C5 on the active tab or must I recreate the named formulas for each tab?

    Thanks in advance!

    1. I also have same issue.

  23. hello!

    How can one create dependent value to drop down list?
    If in cell B3 one chooses one value from drop down list, excel automatically in cell C3 drops dependent value.
    Example - in drop down list I choose customer and automatically in next cell I get address for chosen customer. Is that possible to do in excel?

    1. Hello, Zane,

      Looks like you need VBA. Sorry, we cannot help you with this.

  24. Hi,

    I have a spread sheet with 2 dropdown list and it is working fine using indirect function. Problem is when i erase the value in the first dropdownlist column the second dropdown list value stand still. Is there a way that if I delete the value in the first dropdown the second dropdown will be removed as well?

    Thanks.

    Mike

    1. Hello, Mike,

      Most likely you need a VBA macro that will react to changes in the cell. Sorry, we cannot help you with this task.

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

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

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

  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!

    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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)