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

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

  2. Hey All,

    I am tryig to develop 30 lines in which the user of the excel file can put only certain predefined values. Dependent drop-down lists seem to be a workable solution, but due to structure and dynamics of the data base I cannot apply any of the formulas/codes so far. I am much obliged or any help. Here it is the back ground

    I have a database in a worksheet where:
    - in column A is list of all producer; and
    - in column B next to the producer's name is included list with this producer's products Products are listed in column B and ocupy 3 to 7 cells from column B
    - 1 row contains always only 1 cell with value inside. Either in column A is the name of the producer, or in column B is a product, which is produced by this producer.

    By way of example:
    - Row 1 is for headings;
    - A2 - Producer1;
    - B3 - Product1, B4 - Product2, B5 - Product3
    - A6 - Producer2;
    - B7 - ProductX, B8 - ProductY, B9 - ProductZ
    etc.

    Could someone please help?

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

  4. I have two drop down lists in two different sheets of the same workbook. When I change the value in one dropdown, the other dropdown also should display the same value in the second sheet and when I change value in the second sheet the first one also should change to the same value. Both of the lists contain the same values.How can I do that? Would you help me please? VBA macro would be better for me.

  5. Hello,

    I have successfully created multiple drop down lists for 12 different categories each having 5 options/choices. I used an index-match function to create scores for each category based on what was chosen and then added all categories to receive a total score. My problem is I can't find a of being able to have another drop down list with the customer name where when you select any customer their scores and selections in the other drop down lists would automatically appear/save.
    Thanks!

  6. I have successfully used the example for Dynamic dropdown menus (thank you) and I would like to know if is possible to adapt to fill-in a table with X-Y range of cells where the selection may applied for for any of these cells. So far I can only make it working for rows or columns (take off the "$") in the reference cell setup (fruit in your example).
    Many thanks

  7. Hi!

    Thanks for the usefull information. This explanation is awesome when using just one dependent dropdown. However, I'm working on a sheet with multiple dependent dropdown (B1 is dependent on A1, C1 is dependent on A1 and B1, D1 is dependent on A1, B1 and C1,....

    Any advice on how I should do this?

    Thanks a lot!

  8. How do you create a dependent drop down list for text with space and character such as "/" or "&"?

  9. Hi terry, Please help me, In datavalidation dropdown list which is open by click on mouse but I want to open dropdown list when we select, datavalidation list shouldbe show the option, thank you.

  10. Course| Trainer|Date|Start time|End Time|Duration|Outcome|Notes

    for example the above are rows, is it possible if i select course and it give me all the details horizontally? for example In the Course section I select Employee training, and it give me trainer name, date of training, start time etc?

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

  12. Hi, Is there a way to reference a single primary drop down list....twice? For example if your primary drop down selection is named "vehicle" and one of those selections is "bicycle" then when you select "bicycle" there may be reason to have two non-dependent drop downs like "derailleur type" and "tire size" that would both populate when "bicycle" is selected. The problem I run into is that a named range must be unique...I can only use the primary drop down name once only.

    Thanks

  13. Hi have a hazard library with 500 hazard which I want to pick in a list and then I have contributing factors, controls, risks, description which I want to cascade I have only managed to be able to select the hazard and the second column contributoing factors in one cell I can't copy down and I cannot get it to copy across for the varying lists. Any ideas??
    Thanks

  14. Svetlana -

    Thank you so much for this great tutorial!

    In case you followed the directions to the letter and it's still not working, you may have made the same silly mistake I did:

    =INDIRECT(A2) refers to the cell in which the first drop down list is located, not the named range. So when you're putting in the second drop down list, you're referencing the cell preceding it. I was referencing the list as it appeared in my reference tab. Silly!

  15. Can anyone help please. I need to create 2 pick lists in excel 2010 for translation service as follows:
    select language from (pick list) and select language to (pick list) and the third box should show me a list of translators based on the first 2 selections from the list only. Thanks Same

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

  17. Hi Svetlana,
    I'm trying to load a dropdown list based on doing a VLOOKUP in a data table. For example I load a data table with Orders and Items (only 1 database call) where an Order may have multiple Items.
    I want the user to enter an Order # in a cell, then the corresponding Items are loaded into the dropdown list. I presume I want to load the dropdown using some form of VLOOKUP or Index/Match but can't seem to find a solution. Any help is appreciated!

  18. How to use the same formula in consecutive rows?

  19. Hi,
    If i want to create a sheet where i need to use the cascading dropdown in the consecutive rows, how can I implement it?
    For example, my first row i will select a value from dropdown list In cell A2 and dependent drop down is listed in B2.If I have implement the same in A3 and B3 how should I do it?

  20. I followed the instructions in your example exactly for the dynamic cascading list. However when I select a fruit, no dropdown list appears for exporters. I click on the dropdown button and no dropdown list appears. I can't see anything that I've done wrong. I copied and pasted the name from name manager just to be sure I didn't type it wrong. Any suggestions of likely errors?

  21. Its not working if Named Range values are having space. Deleting the spaces its working..Is there any way to achieve this???

  22. Hello,
    I apply the same way and it works for only different values.
    the problem is I have 3 dependent drop down lists
    the first list contains unique names but the second one has repeated words for each name in the first list and the 3 only depend on them.

  23. I have a long drop down list. Is there a way where typing the first letter will start filtering the drop down list i.e. typing "a" will show only list items starting with "a", continuing typing like "ac" will only show list items starting with "ac" and so on?

  24. Dependent drop down is not working for multiple sheets. Please help

  25. Hello,

    I have a question, I have a dropbox with 2 options. Let's say A and B,
    when I select option B I want to apear one table with prices. And option A have already it diferent table in the main excel sheet.
    I tried but I could not do it.

    Please help!!! ASAP
    Thanx

  26. Hi
    I already have a multiple cascading drop down set.
    I need a second non dependant set from the first but cant seem to get it to work....is it possible to have two independence, dependant cascading sets?
    Set A of drop down relates to cats, Set B relates to dogs

  27. Can you create an indirect function where if you have a list with three items, say, "fruits", "trees" and "other". You want the dropdown list to appear only for "fruits" and "trees". However, once you choose "other" from the list, I want to be able to enter my own text.

    Is this possible? I need a formula or another method rather than indirect function. Please assist.

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

  29. Thanks for the great tutorial.

    One additional function i need to know.
    First column will be DropDown List and second column just shows the specific date.

    Example.

    We have 5 items names

    A
    B
    C
    D
    E

    And Values as follow

    60
    70
    80
    90
    100

    So I want in the first column A DropDown List which should contains a list of (A,B,C,D,E) and When I select any one of them it should show the related value (A=60) (B=70) etc

    Thank You

  30. Thank you Svetlana, I found this very helpful. I was trying it with another posting, but he didn't mention the use of a single word for the titles. Once I changed it - Excel was happy.

  31. Hi,
    I want to know if it is possible to put different lists in the same dropdown.

    Thanks,
    Alex

  32. HI, Your tutorial was excellent for what I needed. One additional item. Is there a way to add a search field to each of the Columns. Some of the lists are long and rather then scrolling, it would be helpful to add a search or filter field when they first click on the drop down menu for either of the first 2 columns.

    Thanks
    David

  33. I have a main validation list consisting of 3 values A, B or C. The second validation list has values of A1 to A5, B1 to B5 and C1 to C5). Is it possible to set the second list to only show values dependent on the choice made from the first. e.g. if I choose A then the second list will only show values A1 to A5. and so on. Is this possible?

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

  35. Hello,

    I have 3 tables with data, that contain some scattered blank rows. I have created data validation lists that can hide these blank rows, until they are populated later on. My question is this:

    Based on the above, can I use some form of an INDIRECT in the data validation process, so that the "correct" list will show up, based on what the user has selected from another column? In the past, I could use the INDIRECT function, if I named the range in the formulas. Because of the formula I am using to hide blank data, I cannot figure out how to attach this to an INDIRECT. Thanks for any help!

  36. Hi

    I want to create a drop down menu that selects a code however within the drop down menu I want to include what the code's description is. However I only need the cell to be populated with the code not the description.

    Supplier Code
    1234 Mountain Warehouse

    Select only 1234 for the cell.

    Is this possible?

  37. hi svetlana,
    how can I make the list to have multiple selection, I need more than one selection - let say for example mango, orange and apple ?

  38. Thanks to this tutorial I have my sheets working exactly as I need them but now I want to go a step further and use the selected item from the drop down to create a product/model number.

    eg A supplier in Pakistan(PA) of Mangoes(MA) was my first ever supplier(001), they also supply Yams(YA)

    so in that example there would be two product numbers PAMA001 & PAYA001

    I have used the same process above to create corresponding tables with the data used to build the product/model number

    where do I look to find a way to do that, if it's actually possible

    cheers Nair

  39. Hi, Im trying to create a Spreadsheet that when i select from a drop down menu in the first coloumn it brings up my selection results in the 2nd coloumn.

  40. Can i make a list (created in one table) dependent on selection from the list (created in other table)

  41. Hello All,

    Is it possible through VBA if i choose the drop down 1 the other drop down goes blank for new dynamic selection through name range.
    please post for any further clarification on the same.

    Reagrds
    Pankaj

  42. how do you put multiple entry in one column for data validation?

  43. Sir,

    I learn alot from your article, first of all thanks so much.
    but i face a problem after creating dependent Drop down, when i select a value from a drop down and almost complete my app entries and then i change in dropdown list it will not updated in the my previous selected selectioon

    like example list

    Fruit
    Oranges
    Apple

    Now i update Apple to Kashmiri Apple, it must be updated everywhere where i selected Apple.

    Please send me solution

  44. Thank you for sharing your knowledge. This combined with t&e vlookup command will fo just what i need.

  45. I have a searchable dropdown list which is not dependent, but, I need to protect sheet. I have already unlocked cells in Format Cells.

    My dropdown list won't work after protecting sheets. I also have some basic VBA coding to allow grouping/ungrouping, and formatting columns after protecting cells.

    My formula for data validation is =BillTo

    My formula for the name 'BillTo' in Name Manager is using =OFFSET(Data!$D$2,,,COUNTIF(Data!$D:$D,"?*"))

    Can you please help me??

    Thank you!

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

  47. Hi,

    Love this tutorial. On my project i need to add a checkbox to the top of a column. If the user selects that checkbox the rest of the data entry cells below become active and visible. If the box remains unchecked then the cells below are shaded out. I don't need to hide them, just color them out in a certain shading so the seem inactive. Can anyone help me with this please?

    Gareth

  48. I've used sumproduct(subtotal(....)) formula on dashboard to dynamically calculate values form a table, by applying filters to the table on sheet2. I need to have filters(dropdown values of filters) on the dashboard, in order to avoid going to sheet2 for applying different filters and instead select a values from dropdown list and it automatically filters the related column in sheet2. I can share the sheet, anyone plz help me.

  49. I have to create a document that displays company names in a dropdown list. Once selected. i want another cell to display a dependent dropdown list to show addresses for the selected company name. The only issue is i want to be able to add/remove company names AS WELL as the addresses listed under each.

    I understand creating a dynamic cascading dropdown list can allow future edits of the addresses but not sure how to do the same for company name. I want to create a file for someone with no excel experience to be able to add/remove both company name and its associated addresses.

  50. Hi,
    I have one master table with an organisation Business units till departments (5 Columns). Now I am looking at the similar Filtering (unique values) functionality based on my list selection in the other dependent cells. If I follow this above method, I end up creating around 120 Named ranges which is next to impossible to remember.
    What is the best way to dynamically get the Unique values depending on the selection of top level hierarchy ?

    The normal filtering in excel on this table shows the unique values, However I want this filtering on the cell that I want the values.

    Pl. help
    Vijay

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