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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. Very helpful. Thanks!

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

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

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

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

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

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

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

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

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

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

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

  29. Perfecto

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

  31. I would like to make a drop down from a table where i select an item # (column A) and the description (column b) automatically appears. Is this possible?

  32. article is very good but i have one query..

    in C column i want to show some data dependent on what select in B column. i know by using indirect function we can do that. but how to implement this logic to entire column? can you please help becuase it is very hard to select each cell update indirect(b2), indirect(b3)...etc...

  33. Not sure if I understood it

  34. It's really weird. I've done exactly what you suggested, but it's not updating automatically - I wonder what I'm doing wrong. I'm using Excel 2013.

  35. Hi all,

    How can add a Validation Button On My Sheet. How can add the sheet Which is in Drop Down List

  36. I want to select data from three drop down list ddl1, ddl2 & ddl3 and the result should be shown by matching the details.

    For example: I have ddl city1 & city2 and third ddl as mode_of_transport.

    When I select a city from city1 and another city from city2 and from third I select mode as ROAD, then the result should display cost of ticket and time taken.

    Is it possible ??

  37. Hello,
    I have the 2 dropdowns. The first se;ects a type of machine, once selected the second will display only the material that works on the machine. When selecting another machine in the first dropdown I would like the second that displays the material to go blank and ready for a selection. Reason is by not going blank the material that is still listed will be associated with the first machine selection making a error. By going blank with a new machine machine is selected in the first dropdown will force the person making the selection to select the correct material thus eleminating the chance of the error.

  38. Great Tutorial Thank you!

    I have 2 worksheets. One has 14 Columns. Each Column has between 2 and 8 items to select. On the next Sheet I have a drop arrow for one cell to choose one of the 14 column items (thanks to your tutorial) However, the next cell needs to give me the items for each column header. I have tried data validation, list, and since my source is on another sheet: =Indirect(SUBSTITUTE(Sheet2!$A$1:$D$7))I then get an error message. Is this a 2 steps process somewhere, or what am I missing?

    Thank you,

    Erin

  39. Hello,
    I followed your article closely and have managed to achieve what I intended. Thanks a ton.
    However, I am unable to apply it to other cells (rows below) if i drag the bottom right of the cell where the formulae are working well.
    What am I doing wrong?
    Thanks in advance,
    Shreeram

  40. I after something similar. My drop down list is either BPay or Bank Deposit. If they select BPay, I want Biller code and Reference to appear in 2 different cells below. Like wise, if Bank Deposit is selected, I want Acc No, Acc Name, Bank Name and BSB to appear in 4 cells below.

    Is this possible?

    Thank you
    Dan

  41. Hi there!

    I have been looking for a way where by if i enter an item's name ie. Bag, in the next cell the price ie. $19.50 would automatically appear. Is it possible on excel?:)

    Thank you!
    -Sarah

  42. Svetlana,

    So I have two columns, each being a drop-down list. I want to be able to change either list, based on the choice in the other cell.

    For example, if i choose "apple" from the drop down list in A1, B1 will then read "red".
    But conversely, i would like to choose "pink" from the drop down list in B1, and have it change A1 to "peach".

    And this applies back and forth so that each column, A and B will be updated no matter which list is chosen from, all the while keeping them drop down lists.

    Let me know if you can help! That'd be amazing

    -Sam

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

  44. Hello,

    What if your dependent variable on the table is a number.
    I am having this issue. Excel says that the dependent name can't be a number, and I need my 3rd column to be dependent on the 2nd column (which is composed by numbers only).

    Thanks

  45. Is it possible to create sub-choices within a drop down list?

    Apple
    - green
    - red
    Mango
    - for juice
    - for pulp
    - fresh
    - tinned

    Similar to the menu > sub-menu > sub-sub menu > sub-sub-sub menu cascade that you have with Windows "All Programs"

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

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

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

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

  50. Hello,

    I am trying to create a pricing list using 2 text drop-down lists.

    Column A "Install" + Column B "Red Cabinet" = $2,500.00 (from a pre-made list)
    Column A "Move" + Column B "Blue Cabinet" = $2,000.00 (from a pre-made list)
    Column A "Remove" + Column B "Green Cabinet" = $500.00 (from a pre-made list)

    Any help you could provide would be greatly appreciated.

    Thanks!

    Lee

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