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 3. Total comments: 329

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

  2. Hello. I'm trying to create a drop-down list with the same selections across multiple columns. Per column, I'd like to hide the previous selections specific to individual columns. The issue I'm having is that if I select an entry in the first column, the entry is now hidden in every column, not just the first column. Any advice?

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

  4. Hi!

    So i need to create a dropdown list based on this condition

    Sheet 1 Column A, the range of list
    Sheet 1 Column B, approval is declared (per row)
    Sheet 2 D5, i need a drop down list where only the approved items on Sheet 1 Column A is listed.
    If it is not approved, it should not show on the list.

    What formula should i write on the data validation?
    Is this possible? Can anyone help me? Thanks in advance.

  5. Hello,
    Kindly help me to add two separate dropdown lists depends on a single dropdown list. For example, Both dropdown menu of State & Delivery time depends on the country dropdown menu.

    Country: State: Delivery time:
    1:India Mumbai 30days for India.
    Odisha
    Chennai

    2:USA Alaska 90days for the USA.
    California
    Florida

  6. Hi,
    Can I use the SUBSTITUTE function within INDEX & MATCH to create dynamic cascading muti-word dropdowns? I did not see an example of combining these two concepts (please let me know if it is there).
    Thanks!

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

  8. Hi All,

    This was very helpful, but my data looks as shown below. The issue is I have to create the dropdown sourcing this sheet and not create a separate data sheet with unique values. Could you please advise how i go about this.

    Currently, I have created a separate data sheet with only unique values and sourced the data from there.
    here is the formula i have used:
    OFFSET('Data sheet'!$B$21,1,MATCH($E3,'Data sheet'!$B$21:$AN$21,0)-1,COUNTA(OFFSET('Data sheet'!$B$21,1,MATCH($E3,'Data sheet'!$B$21:$AN$21,0)-1,45,1)),1) . This works but i have to source it form main sheet and not data sheet...

    Country State City
    India Karnataka Bangalore
    India Karnataka Mysore
    India Karnataka Mangalore
    India Karnataka Davangere
    India TN Chennai
    India TN Ooty
    India TN Kanchi
    India TN Salem
    USA Florida Tallahassee
    USA Florida Tallahassee
    USA Florida Jacksonville
    USA Florida Orlando
    USA California Sacramento
    USA California Los Angeles
    USA California San Francisco

  9. When making the cascading dropdown lists, you cannot use special characters like / (slash) and even - (hyphen or minus) ! Something to note in the instructions.

  10. Hi,

    I was needing to find answer to my question on Data Validation Dynamic Drop-Down List+VLOOKUP. I hope you guys can help me achieve what I want to do.

    So I'll start off by describing what I would like to do. I have different sheets I would like to look up data on. I made normal data validation drop down lists for my PHASE, PURPOSE, & SPECs. I added a bit of a twist to the CATEGORY to where is only shows the amount of rows under the certain spec and there not be empty slots. All these work within the first two sheets which is fine.

    Now for the problem or solution I need to learn;

    I need it to work in the following manner; you select your SPEC, after that the CATEGORY column will display the categories under that SPEC, then your CHOICES column will display the choices under that SPEC+CATEGORY. So every SPEC has it own sheet within the workbook.

    I don't know how to make it work to where the CHOICES column displays whatever is in its corresponding sheet. BTW i only added about 15 SPECs its work in progress in case I had to go a different route.

    I would greatly appreciate any help on this and if I what I am wanting to do is not Dynamic Data Validation Drop Down List + VLOOKUP let me know or if there is an easier way I am all ears.

    Thanks,

    Bravo

    1. Hello!

      This is a complex solution that cannot be found with a single formula. Without seeing your data it is difficult to give you any advice. If you have a specific question about the operation of a function or formula, I will try to answer it.

  11. I am beginner in excel,
    I don't know whether my query will be solved through this method, but first i would like to ask for a solution to a problem that i am facing
    i want to create an excel sheet where in a column i just want to add the name of the content and the rate of that content should follow up along with it.
    in this manner i do not have to continually look up at the rates once i have put the content on it.
    It would be of great help if you could help me find a solution.

  12. Hello,
    I created the cascading drop-down list, but when I ask to show me the second dependent list, it shows me the (right) Header but not the list it contains. Please could you help me to find what's wrong with it ? I did follow your procedure step by step though.

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Check if you have created a named range for each list.

  13. I created dynamic cascading drop-down lists but I'm not able to prevent changes in the primary drop down list using the following formula

    =IF(B2="", Fruit, INDIRECT("FakeList"))

    Is there a way to incorporate the above?

    1. Hi,
      Sorry, it's not quite clear what you are trying to achieve. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

  14. Extremely useful. Thank you.

  15. Svetlana,

    Thanks for sharing this just in time!

    I am able to design my sheet!

  16. I am not that experienced with EXCEL. However I do use it to generate quick prototypes for business applications. I use these prototypes to help extract the detailed functional requirements for the users to feed into the design of the production business system application.

    One reoccuring feature is the requirement for a cascading drop down box.

    The first "Parent " drop down would be a parts list.
    This Parent list could have hundreds of entries for the user to select from.

    The second (child) Dropdown box contains the LOT Numbers for all of the products.

    Therefore the user first selects the Part Number using the Parent Drop Down BOX,

    Then the user will use the second drop down box to select the LOT NUmber.

    There are normally only 5 or 6 different LOT Numbers for each of the several hundred Part Numbers.

    It seems that the use of the EXCEL OFFSET, MATCH, and INDEX functions just cannot do the job.
    (But I am not an expert)

    What strategy should I follow to accomplish this.
    Trying not to use VBA Macros if possible.

    Thank you for any help
    Chuck

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please reread the article above, it covers your case completely. Thank you.

  17. Thank you! Finally someone with a solution to my problem!

  18. In data validation my source are digit ( numbers ) ,hence I get error message as "The source currently evaluates an error ",Do you want to continue ? .How to solve it . If the source is a text then it works fine ,but if it is a number it dosn't work.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  19. I want nested dropdown list.
    Example:
    Fruit >Apple
    >Grapes
    >Mango
    Country >India
    >US

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

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

          1. Hello Lin!
            I do not see your data. Therefore, I can’t name the exact reason. However, remember that the names of your items from the drop-down list must exactly match the names of the named ranges. If the name is "Procedure or Protocol", and the named range is "Procedure_or_Protocol" - this is 100% not working.

            1. Good Evening Alexander. If I'm not mistaken a named range can't have spaces, so how do I make the items in the drop-down match exactly with the named ranges. I was of the understanding that the _ represented a space. How else can I make this work? Would it be better for me to send a copy to you to look at the data or is there another option? I appreciate your time and assistance with this matter.

              1. Hello Lin!
                Items in the drop-down list must contain _ instead of a space For instance, Procedure_or_Protocol. Your items from the drop-down list must exactly match the names of the named ranges.
                I hope this will help

    2. Hello Lin!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

  22. Is it possible to create a dropdown menu that will automatically fill column A, B, C & D? Basically, I need to make one selection, and have the first four columns autofill.

  23. Thank you!!!! This was a blessing and helped so much!!!!!

  24. Nice article, but stuck. I have a drop down (master) that needs to change three other cells once you change the drop down.
    Ex. Historical list A_L, I_N, X_R, R_T
    Hours/tops 174, 47.2, 88.3, 13.2
    Metric Type Joy, Rain, Sun, Sad
    Source Joy.xls, Rain_GL.xls, Sun_2020.xls, Sad.doc
    If Historical List changes A_L to X_R
    The other need to go from A_L, 174, Joy, Joy.xls to X_R, 88.3, Sun,Sun_2020.xls

    I tried making the first Historical List the primary, and the others customer with a =if(B3=C7:C11,D77:D11,0) where B3 = Cell that changes from any given C7-C11 cells (array) and the D column hours/tops. I didn't continue because that didn't work. I tried various others ways, and I am just frustrated.

    1. Hello Kristy!
      I'm sorry, it is not very clear what result you want to get. 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.

  25. Thanks Svetlana.. though this article was little bit tricky but i have enjoyed it..

  26. hi, is it possible to show 2 named ranges together using data validation. For example list A list of fruits and list B has list of vegetables. in dependent drop down list. we can get if A1 has fruits then List A, and if A1 has Vegetables then List B.
    But if A1 = c , i want to the list of both list A and list B, is it possible to do without creating named range including both list.

  27. A question already asked but I didn't find a response ...

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

  28. i have a problem that is difficult to explain but may well be very easy to solve, my biggest constraint in what i am trying to achieve is that macro is not allowed to be used as the template i am creating is uploaded into a platform that cant accept macros.
    Having said that, this is what i want to do: I am using a dependent dropdown list so when i select a particular cell in (column A) with a list, that selection informs another cell adjacent in Column B (another dropdown list) only certain value is available for selection list. I want now to complicate it further because i want to add another dropdown list in column C with a selection of A, B, C etc and hope that when i select A, B or C it will concatenate the data in column B cell. To explain it better, if i select Column A (fruit) Column B list would contain Apple, Pear, Plum, Orange but what if i want to identify the colour of the fruits i would have the colours available in Column C; red, blue, green and pink, so i want to have the option of adding the colours to the fruits in Column B cell.

  29. Thanks a lot! Great tutorial, especially for dynamic drop down!!

  30. Hello,
    I want to have a dependent list based on multiple selections from the first list. How do i make the second list only show values associated with the multiple values in the first?
    Example:
    1st list is "categories" and you can select multiple answers ("Arts, Business"). 2nd column would provide drop-downs for both "Arts, Business" ("design" (within "arts"), "investing"(within "business").
    Another example:
    List A
    A1 "X"
    A2 "X1"
    A3
    Multi-select A1 and A2
    Show values with X and X1 to multi-select.

  31. Hi,
    Im trying to create a dependent dropdown where the secondary dropdown elements can come multiple types based on primary selection. Eg: If primary dropdown is Apple, Orange and Grapes and second dropdown is China, Brazil and Australia. If I select Apple - say Australia and Brazil should come and I select Orange it should again populate to China and Australia. Is there any way of doing this as creating a range table for every single dropdown is a tedious task and doesnt work with long dropdowns?

  32. I believe there is an easier way to prevent user from changing the value in the first dropdown after making the selection in the second menu (i.e. to prevent mismatches in choices in the primary and secondary dropdowns), easier than the other tutorial linked and definitely easier than VBA:

    You can block changes in the first box (or, at least, force the user to clear the second selection before toggling the first dropdown again): changing the formula for the first drop down to =IF(B2="",fruit_list,INDIRECT("fakerange")), as suggested for the simple formula.

    For UX, I added a comment to the B2 column header in my actual working sheet (not my data validation sheet) that says "To change the fruit of a line item, first clear the selection in column C by selecting the 'Exporter' chosen, then hit 'delete'". That way, you prevent mismatches, allow the flexibility for changes, empower the user to work autonomously, AND simplify your work considerably. It won't work in EVERY circumstance, but in most, I think this is a much simpler solution.

  33. scrolling toolbar is quite obnoxious. copied/pasted the text I wanted to Word and then went elsewhere. seriously...

  34. what if I have like 3 in the first list, 8 in the second list and 5 in the last list
    BUT

    I have around 705 total projects

    will I need to create a 3x8x5x705= 84 600 data in total for it to work?

  35. I can't even pay attention to the content of the article because of the scrolling ad at the bottom of the screen that makes me nauseous.

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

  37. Hi, I have created one spreadsheet, where I am selecting employee ID and based on the employee ID all the data will get populated in other cells. I want to clear all cell value if I change the employee ID. Can any of you help me to get it done.

    Thanks in advance

  38. I am trying to create a spreadsheet that allows me to choose a Job Number and then, from the job number selected, will allow me to choose a Phase code specific to that job.

    I am running into erros every time i input the =INDIRECT(A2) data validation and can't get past it.

    it is very hard for me to get my Job Number to be corresponding with the Phase codes.

  39. This was super helpful. Thank you so much!

  40. Great Article - Thanks!

  41. Thank You for the article!!

  42. Hey,

    I was just wondering how I could add a third dynamic cascading drop-down, based on options that show up in the second drop-down.

    Thanks

  43. Hi,

    How would you set this up by having attributes running on rows rather than over columns. The dropdowns need to run for new rows added.

    Regards

  44. Hello,

    I have two questions:

    -I followed the "Exclude blank rows from the dynamic cascading dropdown" instructions and I don't have blanks, but the title/header is in my second dropdown box. Is there a way to not have it?

    -I am also working with 5 variables. Is there a way to reference a complete table for each dropdown? Or do I have to make independent lists for everything?

    Thank you!

  45. Say we have 2 Cells C7 & D7 each have a Data Validation. Well if we change the value of the Cell in the C7 then the Value of the Cell D7 should also Change according to the corresponding List of C7 or turn blank. But the value of D7 always remain the previous value of Data Validation changed in C7. We have to Change D7 manually later after changing C7. Can it be possible without VB.

  46. Hi,

    I have a table in which I wish to use each column to restrict the choices for the next column, which restricts the choices for the next column, and so on. A lot of the information in each column is repeated, rather than being unique values as listed here. Can you possibly help with this?

  47. I have a table containing first two columns having two indipendent drop-down lists. I like to have a drop-down list in the third column depending on the values of the first two cells.
    Please advice.

  48. I made for myself a simpler 2 step solution for detecting primary and secondary dropdowns that mismatch due to changing primary after secondary has been selected:

    Step 1: start primary and secondary choices with matching numbers i.e., primary choices 1 - Apricot, 2 - Mango, 3 - Oranges; secondary example 2.1 - Philippines.

    Step 2: Use conditional formatting to make the secondary choice cell change color (say turn bright red) if first left digit of primary not equal to first left digit of secondary, i.e. 3 - Oranges with 2.1 - Philippines, 3 ≠ 2, secondary choice cell's background turns red.

    We can also use conditional cell formatting to show a glaringly different color if no choices have been made yet.

    Jossy

  49. Thanks for this post, this is a really interesting approach. I use Excel tables and pivot tables quite often, and so a cascaded dropdown set would be most useful as the validation for a pair of table columns.

    So as an example, say I had a second table to collect fruit inspections with columns:
    Inspection Number | Fruit Observed | Country of Origin | Inspection Result

    I'd like to put the Fruit dropdown in the Fruit Observed, and have that cascade to the Country of Origin field on the same row.

    Is this feasible?

    Jeff

  50. totally awesome! Thanks a lot for this article

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