Create Multiple Dependent Drop Down Lists

Share
Embed
  • Published on Sep 26, 2014
  • Create multiple dependent drop down lists in Excel. Each lists options are dependent upon the previous list selection.
    Learn loads more Excel skills - goo.gl/dZQhpg
    It is vital for your Excel reports that data is accurate. Assist users when entering data by providing drop down lists of the possible options. If you have large lists of options then break it down into several cascading drop down lists.
    To create multiple dependent drop down lists in Excel you will first need to set up your lists as named ranges. The Data Validation tool and the INDIRECT function can then be used to reference the named ranges creating dependent lists.
    Join the Audience:
    ★ Twitter: computergaga1
    ★Facebook: computergaga
    ★ Google + plus.google.com/+Computergaga
    View more awesome FREE Excel tips
    www.computergaga.com/blog/
  • Howto & StyleHowto & Style

Comments • 470

  • AFZAL ZAMIR ANSARI
    AFZAL ZAMIR ANSARI 13 days ago +1

    How are you sir ? Are you okhay I am waiting for your new video.

    • Computergaga
      Computergaga  13 days ago +1

      Really good Afzal. Should have a new one coming soon.

  • Sir Corn
    Sir Corn 18 days ago

    Another pearl that does to my Excel knowledge collection. Thanks

  • Ready0123
    Ready0123 21 day ago

    Thank you so much !! This was VERY useful to me.

  • Paulina GC
    Paulina GC Month ago

    Incredible!! Thank you so much!

  • Stan Lard
    Stan Lard Month ago

    Great video and well presented. I would like to ask if there is a way to add multiple cells (multiple columns) with one selection from a dropdown selection? Example: I have a header with last name, first name, date of birth, passport number, etc. I would like to select the person by name and the rest of the info fill in across columns A-H, in proper order and format under the correct header. Or I would like to select someone by name from a dropdown list and have have all their data auto fill on a table that you would use in MS Word. Thank you in advanced.

    • Stan Lard
      Stan Lard Month ago

      @Computergaga you are amazing Sir and thank you!

    • Computergaga
      Computergaga  Month ago +1

      In Excel, this would be a VLOOKUP formula. You select the name from a drop down. VLOOKUPs in each of the other columns (dob, passport number etc) fill them in.

  • mohan kumar
    mohan kumar Month ago

    Cell are not getting blank after removal /changing the content in adjacent cell,

    • Computergaga
      Computergaga  Month ago

      Not automatically no. Unless that is an option in the second drop down.

  • Padma Naga
    Padma Naga Month ago

    Ur explanation is awesome sir... But I can't understand ur language ... But I use to see no.of times... Then I will understand ... Thnq sir

    • Computergaga
      Computergaga  Month ago

      You're welcome Padma. I hope that you begin to understand me better.

  • Hany Fouda
    Hany Fouda Month ago

    Thanks for the very useful video , really it makes my day

  • Leena Samman
    Leena Samman Month ago +2

    Thank you! This was very helpful

  • Anjani Vyas
    Anjani Vyas 2 months ago

    It's really helpful video. I watched similar type of session on different TheXvid channels but Alan, you made it very simple. Thanks much!!

  • nithya chandran
    nithya chandran 2 months ago +2

    superb.. It was very very helpful. Thank you

  • kaskej
    kaskej 3 months ago

    thanks it is very useful

  • Tran Ngoc Trung
    Tran Ngoc Trung 3 months ago

    Thanks for sharing

  • Max Gatsche
    Max Gatsche 3 months ago

    Great help. Regards

  • 2Activ Life
    2Activ Life 4 months ago

    Great help!!

  • Mahaboob Subhani
    Mahaboob Subhani 4 months ago

    Thank you for sharing this video. Quick question in regards to drop down list in data validation function, can we insert silcer? Reply will be appreciated thank you in advance

    • Computergaga
      Computergaga  4 months ago

      You're welcome Mahaboob.

    • Mahaboob Subhani
      Mahaboob Subhani 4 months ago

      @Computergaga thank you so much for your response

    • Mahaboob Subhani
      Mahaboob Subhani 4 months ago +1

      @Computergaga thank you very much for your response

    • Computergaga
      Computergaga  4 months ago

      You can use slicers with data formatted as a Table so I guess if the validation was in columns of a table yes. A Slicer is a filter so a different tool to Data Validation, so I'm unclear as to the context you would like to use it. But in theory they can work together sure.

  • Sherwin Sibbaluca
    Sherwin Sibbaluca 4 months ago +1

    thumbs up... one question... how to automatic clear the list of list 2 and list 3 if i changed the list of list 1? thank you.

    • Computergaga
      Computergaga  4 months ago +1

      This would require a macro on the worksheet change event. So when content is cleared from list one it automatically clears others.

  • Azhar Elbi
    Azhar Elbi 4 months ago

    Thank you so much!

  • M AZEEM
    M AZEEM 5 months ago

    Thank You So Much Sir

  • Pavel Panfil
    Pavel Panfil 6 months ago +1

    Thank you very much. It was very helpful. Have a great day!

  • Alee Khan
    Alee Khan 6 months ago

    how can we create 3rd list based on car category and transmission
    e.g.
    toyota>automatic>Gli
    but i also have
    honda>automatic>civic
    also Gli comes with 3 colors i.e. black, white, sliver and also with 2 engines 1300cc and 1600cc how can we create 4 -5 lists based on this data

    • pavitra nanda
      pavitra nanda 6 months ago

      It is definitely possible to do.

    • pavitra nanda
      pavitra nanda 6 months ago

      ​@Alee Khan The "Tree algorithm" must be clear
      1) Toyota ->automatic/Non --automatic
      2) Automatic -> Car_type
      3)Car_type->Honda,Corolla,Civic
      4) Honda->Engine/colour Corolla->Engine/colour Civic- >Engine/colour
      5) Color -> Black,White,silver
      6)Engine->1600cc/1300cc and so on.

    • Alee Khan
      Alee Khan 6 months ago

      @pavitra nanda how
      aa we cannot make 2 lists under same name.
      .
      is there any other way.
      if i select automatic under toyota it show gli vitz
      and if i select automatic under honda it show civic and city

    • pavitra nanda
      pavitra nanda 6 months ago

      yes, it is possible

  • tanu rakshit
    tanu rakshit 6 months ago

    Superb .Have seen many Videos on multiple dependent drop down but they were sooooo complicated & confusing .Your explanation & presentation has made it crystal clear.Thank you so much for the video.Cant thank you enough.:)

  • Prabu Saul
    Prabu Saul 6 months ago

    BUT IF I USE SPACE, ITS NOT WORKING

    • Computergaga
      Computergaga  6 months ago

      I have a video with a solution for this - thexvid.com/video/aidOWOsMdD8/video.html

  • Tejashree Deokar
    Tejashree Deokar 6 months ago +1

    Thank you!

  • Tom Manly
    Tom Manly 6 months ago +1

    Thanks for this! Much easier to understand than other examples that I have seen around!

  • Selma Inan
    Selma Inan 6 months ago +1

    Thanks for help. It was really helpful.. Super explanation...

  • tayeb rizvi
    tayeb rizvi 6 months ago

    There is one loop whole i.e if we select value in column A then column B cell is not getting blank, I fact it retains the old value. The previous value of other category

  • Will Jones
    Will Jones 6 months ago +1

    You are an absolute hero. I know this is an old video but I want you to know that this brilliant work is still being used today. Thanks for your help

  • Shruti Suryanarayana
    Shruti Suryanarayana 7 months ago +1

    I wanted to create a very detailed expense chart but didn't know how to create dependent lists. Thanks to you, i did it in just a few minutes. The way you explain stuff is very good, even someone with very less excel knowledge can do it easily.

  • David Galloza
    David Galloza 7 months ago +2

    Bro, thank you, I've been looking for something like this for hours.

  • J. B. Wagler
    J. B. Wagler 7 months ago +1

    Thanks, this was a great help!!!

  • Salah Aldeen
    Salah Aldeen 8 months ago +1

    great help, ta mate

    • Computergaga
      Computergaga  8 months ago

      You're very welcome, thank you Salah.

  • imen aoussaji
    imen aoussaji 8 months ago

    Hello
    Thank you very much for the video. It was super helpful! However, I am not able to name my list 2 as list 1 since the elements in List 1 have characters like space, / etc. for example: Media Costs. I would have to take off the space. So, the name box in excel rejects the name since it has forbidden characters. Do you know any solution for this?
    Thank you!
    Imen

    • Computergaga
      Computergaga  8 months ago

      Hi Imen, yes I have a solution to the space issue in this video - thexvid.com/video/aidOWOsMdD8/video.html

  • Leigh Baker
    Leigh Baker 8 months ago +1

    Very easy to follow along. Thanks

  • Sumat Sethi
    Sumat Sethi 8 months ago +1

    Putto phalo. Thank you so much

  • Pankaj Sharma
    Pankaj Sharma 8 months ago +1

    Nice way to explain..

  • Kevin Moonegan
    Kevin Moonegan 8 months ago

    Hi, how can we use drop down list to get a value. for example: drop down list done, i select 1 category(expense). result i want is THE AMOUNT IN NEXT CELL APPEARS AUTOMATICALLY

  • kabir750
    kabir750 8 months ago

    Fantastic video - when i choose new category in the firs list- Can I automatically hide the previous result in the second list? Because if I do not select ןמ the second list .then the first result remains

    • Computergaga
      Computergaga  8 months ago

      You would probably need a macro for this because you would need to test if the value in the first list has been changed or not, and a formula can't handle that. The Worksheet Change event can be used.

  • Mukesh Mishraa
    Mukesh Mishraa 8 months ago +1

    Exceptional

  • Awaking Beauty
    Awaking Beauty 9 months ago +1

    LOVE ! Thank you

  • Tejas Punjani
    Tejas Punjani 9 months ago +1

    Great efforts and well explained.. Keep it up.!

  • Vince Rivas
    Vince Rivas 9 months ago +1

    Very helpful video! I was personally experiencing an issue with a dependent list I had created, in which I had a bunch of blank cells after each list. Your demonstration on use of the name ranges in the indirect formula helped to resolve this. Much appreciated!

  • bpkingcobra
    bpkingcobra 9 months ago

    This was very helpful Thank you. I'm creating a similar list but I have a question...using your example, you have Books under Category...what if you also had Books in another column, Would it cause problems when naming your ranges? Also, how would naming ranges work if for example instead of Games you had Computer Games under Category? Do you have to use underscores to separate the words?

    • bpkingcobra
      bpkingcobra 9 months ago

      @Computergaga Thank you!

    • Computergaga
      Computergaga  9 months ago +1

      The named ranges would need to be unique, so you may need to be clever with the names. With the space issue, yes an underscore can be used to separate words. But you don't need the underscore in the list item, just the named range. You can replace it with the SUBSTITUTE function shown in this video to solve this problem - thexvid.com/video/aidOWOsMdD8/video.html

  • mohamed rashad
    mohamed rashad 9 months ago

    Thank you dear. but there is a problem when I modify an entered data in a main cell, the sub cell remain e.g. if the written data was (Books-Romance-elephant moon) and I modified books to films, Romance-elephant moon would remained. Any ideas for this problem? Thank You

  • EuReGme
    EuReGme 9 months ago +1

    this was a very helpful video. thank you so very very much.

  • flames 8889
    flames 8889 10 months ago +1

    This video saved my ass. Thanks, mate.

  • harshul seth
    harshul seth 10 months ago

    really appreciate it.. i was banging my head since a day just to remove $sign before numerics.
    what this $ sign in excel indicates?

    • Computergaga
      Computergaga  10 months ago

      In a formula it indicates an absolute address or reference. This is a cell reference that does not move when a formula is copied. I have a video on them here - thexvid.com/video/b2wcFAb0v8k/video.html

  • Muaathe Aziz
    Muaathe Aziz 11 months ago

    I have a question and I do not know if this job is possible or not by excel, a job is included a number of students they have to chose three options of supervisor and the report will include a competition between the students whose chose the same person according to their grade. Number the students for a supervisor will be just three so if there is 5 students chose the same person there will be two students will move to their next choice for another competition and so on. I do not know what is the name of such process and I hope excel can do it and advise will be appreciate

    • Muaathe Aziz
      Muaathe Aziz 10 months ago

      @Computergaga thank you so much any advice for an example pls

    • Computergaga
      Computergaga  10 months ago

      Excel could do this, but it would required some logical formulae to assign the correct choice per student. Not necessarily simple.

  • The Ascend Company
    The Ascend Company 11 months ago

    Great video, my only issue, I need to have items with spaces between them as name ranges, List 1 = Regions, contains Region 1, Region 2..... List 2 Region 1 "but can't do thas an error occurs when setting range name, is there a way around this? It works perfectly without​ spaces.

    • Computergaga
      Computergaga  11 months ago

      I have a video on the problem caused my spaces and how to solve it here - thexvid.com/video/aidOWOsMdD8/video.html

  • İsmail Tokmak
    İsmail Tokmak 11 months ago +1

    thx!

  • Jayce Ng
    Jayce Ng Year ago

    Hi! Thanks for this but I'm wondering if my 2nd lists are all the same selection e.g. positive, neutral and negative for different choices in list 1, and i want to extract data based on these 2 lists. How do i do that further on?

    • Computergaga
      Computergaga  Year ago

      In the tutorial it uses the values from cells B1 and B2 in the formula. You would do the similar depending on your sheet layout and criteria. But using the cells of the two drop downs.

    • Jayce Ng
      Jayce Ng Year ago

      Thank you for your response but how do i combine this 2 dropdown with this other tutorial?

    • Computergaga
      Computergaga  Year ago

      Sounds like you want a multiple column lookup for retrieve cell information related to selections in your first and second drop downs. This tutorial will show you how - www.computergaga.com/tips/lookup_formulas/multiple_condition_lookup_formula.html

  • Denny Guerrero
    Denny Guerrero Year ago +1

    Thank you! This is exactly what I need for a template I'm working on at my new job!

  • Kaneo -
    Kaneo - Year ago

    my data has only numbers, for ex: if the 2nd dropdown chose number 10 and then the 3rd dropdown will have options for "10" list. but it doesn't work :(

    • Computergaga
      Computergaga  Year ago

      You cannot use numbers as named ranges. So we would have to be clever and maybe put an underscore before the named ranges such as _10 and _12.
      Then when someone chooses 10 from the list (pretend the list is in cell A2) the Data Validation rule could be =INDIRECT("_"&A2) to concatenate the unscore to the number selection to match the named range.

  • Chris Lewis
    Chris Lewis Year ago +1

    Super thanks

  • vanderley nhantumbo
    vanderley nhantumbo Year ago +1

    Thanks it worked perfectly

  • 올겐매니아
    올겐매니아 Year ago +1

    Thanks for the explanation.

  • Rohit Parab
    Rohit Parab Year ago +1

    Thanks buddy, you helped a lot

  • Ignasius Bagus Satrio Suhardiyono

    Love this! Can't describe how it helps! Super thank you :D

  • Siva Bala
    Siva Bala Year ago +1

    Thank you

  • Steve Groot
    Steve Groot Year ago +1

    Thank you. Very easy to understand.