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

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

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

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

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

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

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

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

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

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

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

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

    Thanks,
    Alex

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. I want to run a macro on the basis of different selections from drop down list, is this possibl?

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

  33. I want to make a list using the following:

    I have a category for Ex. Business
    And i Want to show the different type of business by simply clicking on the category of business

    can you help me how to make this in excel.

    Thank you.

  34. I have a drop down list A1 with Name, List B1 is phone number, C1 is Text, and D1 is Email. After I click a name I would like the corresponding phone number, Text(just the word) and Email(Just the word)in the next drop down. Also be able to choose the same name with a couple of times but with either number, test and or email I can attached a copy of what i have if need be

  35. I have created a drop-down list on a master sheet. I have 22 sheets with identical columns to the master sheet. The only difference between these sheets is how many rows there will be with data entered. (Some cells in some rows will be blank- this is necessary.) Can you please explain how I can have a drop-down selection link to a specific worksheet and populate the current sheet (in this case it would be the master sheet) with the data in the cells from the sheet selected in the drop-down? For example, I would like to select a rep's name in the drop-down box, and it populates with that rep's sheet (the rep's sheet will have a 4 digit number as the name of the sheet). Any and all assistance is greatly appreciated. Thank you.

  36. I have created my lists.
    I have the primary drop-down working!
    My 2nd drop-down isn't working. I have my ranges named using "offset" formula as the lists may frequently change. When I make the secondary list NOT using offset, it seems to work okay.

    Any tips?

    1. Hello, Trey,

      To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  37. Very helpful. Thanks!

  38. i have small problem in excel. it is i am created a drop down in the excel. like this Ex: B is 1,2,3,4,5.... and C is 45,56,59,58,26..
    but iam select B1 then automatically C Cell become changed into 45.
    how is it please solve my problem. thanking you

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

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

  41. It was a great article it really helped me alot.Thanks alot for you help. I really appreciate your time and help. Thanks again :-) buddy

  42. I just want to say thank you for your generosity. This tips will make me perform better at work. I made a search for this in the internet for a while, and your website was the only one to post the exact answer.

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

  44. I have 2 columns in a spreadsheet, both are picked from a drop down list.

    Col A is "Do you want more information" - Possible Values Yes\No

    Col B is "How did you hear about us" - Possible values are Email\Phone\Other

    Question: If "No" is chosen in Col A, then Col B's value should be N/A, otherwise the user should be able to choose from the dropdown and pick a value. Using the following formula, I can get N/A to appear, but the "false" option overwrites any available dropdown values or previously selected values. So simply put, if Yes is chosen, let user choose a value in the other cell. If No is chosen, show N/A in the other cell. Thoughts on how I can get this to work? or how it's work with data validation?

  45. Thanks for the tutorial.
    Help me a lot with my problem.

  46. Sir,

    Please help me to get dropdown list based on the followings:
    I have a list of names, how to create dropdown list to populate the list of the words to display when the first, then second letter (and so on) of the word typed.

  47. Good day,

    Thank you soo much for the assistance. I have managed to create a multiple word cascading drop down list. The problem is that the result on the dependent list only references to whatever is on the first row of the main drop down list. How can i correct this?

  48. I have one question.

    I have Banks
    Abank
    Bbank
    Cbank

    And Eachbank has same currency
    USD
    GBP
    EUR
    CHF
    JPY

    And Each Currency has some special Number
    Abank USD SPECIAL NO 111
    Bbank USD SPECIAL N0 112
    Cbank USD SPECIAL NO 113
    same for EUR,CHF,JPY,GBP

    I this stiuation

    When i selected First Dropdown i wanted to show Banks-

    Second dropdown wanted to show All currencies-

    Third dropdown wanted to show Special no

    Let me give you and example
    IF Abank selected on DRPList1
    and USD selected on DRPList2
    show only ABANK USD SPECIAL NO 111 on DRPList3

    IF Bbank selected on DRPList1
    and USD selected on DRPList2
    show only BBANK USD SPECIAL NO 111 on DRPList3

    But it is always showing ABANK USD SPECIAL NO 111 DRPList3

    So how can i connect lists if my banks has same currencies and this each same currencies has different special number

    Thanks

  49. Perfecto

  50. This is an wonderful illustration, thanks! But I'm getting stuck in a problem. My requirement is to create a task and sub-task list and populate that on daily basis. I mean master sheet will contain tasks like Functional Design, Technical Design etc.; sub-tasks like preparation, review, rework etc. There will data validation in user input sheet where user will select a task on daily basis and a sub-task will be populated accordingly. Above example is working only for a particular row. Because "fruit" refers to cell "Sheet2!$B$1". What do I need to do to make it applicable for Sheet2!$B$2..$B$10 and so on?

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