Google Sheets - Drop Down List, 2 Dependent Dropdown Lists

Share
Embed
  • Published on Dec 27, 2017
  • Learn how to make dependent dropdown data validation lists and have different dropdown values based on another cell.

Comments • 121

  • Aniqa Moinuddin
    Aniqa Moinuddin 2 days ago

    Range names cant have spaces - so this would break with the Land Rover make... and most other use cases..

    • Diego Castillo
      Diego Castillo 19 minutes ago

      You can just put a dash in between the words. Land-Rover

  • Yuval Shukron
    Yuval Shukron 17 days ago

    How do i apply it to the rest of the cells on the main column?

  • Ryan Panes
    Ryan Panes 19 days ago

    i thinkwe can merge the indirect function to the custo list so that we can avoid the extaneous range am an excel user btw.

  • Ahamed Jameel
    Ahamed Jameel 20 days ago +1

    This is one of the coolest things I know in Excel.

  • Imran Hashmi
    Imran Hashmi 24 days ago

    Can i Get this Dependent Drop Down list in Google Forms? Please

  • Nicholas Azoya
    Nicholas Azoya Month ago

    Thank you for the tutorial.

  • david wharrier
    david wharrier Month ago

    im wanting to do something similar but i want to use data from 3 seperate drop down menus to get the final drop down menu (eg, car make, model to get the data of what extras tyou can get for that particular model) is there any way to do it on google sheets?

  • Dr. Ravi SK
    Dr. Ravi SK Month ago

    Very nice

  • Hernan Wagner
    Hernan Wagner 2 months ago

    Thank you so much!! Great tutorial!!!

  • Hemanth K
    Hemanth K 2 months ago

    Thanks for the video.

  • Grant Sparks
    Grant Sparks 2 months ago

    What if your "make" category is over two words. How do you define a name range? Thank you

    • Lucas Carlson
      Lucas Carlson Month ago

      ya saw this too, find an answer? only fix for me is an underscore so far

  • ADMIN-CT
    ADMIN-CT 2 months ago

    What if...

    I have 2 down Lists. One Called "Artist Name" and other Called "Company Name."

    I'd like to filter by selecting the "Artist Name" (Cell F2) and by "Company Name" (Cell D2) like Below...
    =SORT(QUERY('Master Queue'!B3:U, "SELECT * WHERE (D = """&F2&""" AND S = """&D2&""")

    Now what if I wanted to search by only "Artist Name" w/o "Company Name" affecting my sort or vice versa?

    Example: IF "Artist Name" then show Artist Name. IF "Artist Name AND "Company Name" show both. IF "Company Name" then show only company name.

    I would assume that if the Cell contained nothing THEN only show the Cell that does? I don't know how to do it.

    • ADMIN-CT
      ADMIN-CT 2 months ago

      @Learn Google Spreadsheets Okay, I will.

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  2 months ago

      Watch my dashboard videos, I think I have an example exactly like that or very similar if I'm not mistaken.

  • Jennifer Grove
    Jennifer Grove 2 months ago

    Magic!

  • Brian Mwevi
    Brian Mwevi 2 months ago

    Thank you very much for the tutorial. Very helpful!

  • Google Sheets Training - Brian Wochele

    Great tip about leaving room in your range to add items in the future since blanks won't be included in the drop down.

  • S Modak
    S Modak 3 months ago

    Wow. This is very helpful. Very much what I was looking for.
    However, how do I go about replicating that row down till the last row? Considering that my data is a different or very row has a different manufacturer and make? Will all the formula in this tutorial hold good even then? When replicated!?
    A response would be greatly appreciated
    :)

  • Jhoni Rosales
    Jhoni Rosales 3 months ago

    Wow! Thank you

  • KALLAPPA DHANGAR
    KALLAPPA DHANGAR 3 months ago

    Hey Thanks for video, But i wanna do same for Google form where I wanna connect the data in dependent like this please help me out

  • Meryem Ormancı
    Meryem Ormancı 3 months ago +1

    Harikasın. Süper!!! Thank you very very much.
    I like this

  • Christopher Bacani
    Christopher Bacani 4 months ago

    How do you add rows?

  • Ageu Matheus
    Ageu Matheus 5 months ago

    Thanks!!!!!!

  • Steve Matta
    Steve Matta 5 months ago

    Works great for one row. But what if you have a list of people, say karate students, each with a pull-down for rank (beg, int, adv, black) and a contingent weight division indirectly linked to rank (light, light-middle, middle, middle-heavy, heavy). Do I have to construct this for every student? I tried using the indirect function in the data validation window and I get an error.

  • Juan Aguilar
    Juan Aguilar 5 months ago

    Is it possible to create a Drop Down List directly from another Google Sheet file? Thank you.

  • omar khaled
    omar khaled 5 months ago +2

    In excel we can do it easily, if I have a column full of drop down how to do it ? In excel we enter it (Indirect formula) directly in data validation bar, how to do the same in google docs?

  • ValmisFilm
    ValmisFilm 5 months ago

    Cool! I used OFFSET for this ( like =UNIQUE(OFFSET(J79:J82,0,MATCH(B81,J78:M78,0))) ) but this is much better and easier. Also - have you tried to make like A, B and C kind of tree dropdowns? I mean you would have here also an option for every model of the car as well that are different for each B value. As in they are the C. You get it?

  • Reuben Smith
    Reuben Smith 6 months ago +21

    This only works in one cell. How does this work for an entire column of dynamic/dependent data validations?

    • demonfire691
      demonfire691 Month ago

      @karla avalos docs.google.com/spreadsheets/d/1O-1-78fEBReO2v37MyiOQm11B_epz_8bu6Fxb1oQ-EQ/edit#gid=0
      There will be error flags whenever the second dropdown list data changes, but this is what I do now. Both formulas I use are on the second tab. The Cell function above is in A1 and a Query function i've started using is located in B1. I've stopped using the Indirect function because the Query function replaced it.

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  Month ago

      thexvid.com/video/s-I8Z4nTDak/video.html

    • karla avalos
      karla avalos Month ago

      @demonfire691 can you share a sheet where you use this formula just to observe how do you do it?

    • demonfire691
      demonfire691 4 months ago +1

      I did it by using this function with the indirect function; =iferror(Cell("contents",index(A3:A,MATCH(false,isblank(A3:A),1))),"")
      Instead of using indirect on the cell with the drop down list, use it on the cell with this formula in it and it should change as you fill up your list.

  • Josh Lazo
    Josh Lazo 6 months ago

    This works if I only need a single dropdown, but what if (using this example) I'm making an inventory list for the dealership and want to have multiple rows displaying the same dependent dropdowns based on what the A column is displaying in that row? I'm trying to build a similar doc that is shared on a google drive. I can make repeat the drop downs in excel, but I haven't figured out the Google Sheets version of it.

    Thanks!

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  6 months ago

      I have a video doing this with a script. There is no easy way of doing it in Google Sheets. thexvid.com/video/1SIN5NyQ9fw/video.html

  • Selva Sundaram
    Selva Sundaram 6 months ago +1

    Zoho Sheet provides formula-based Data Validation, so the little list you had to create on the side is redundant. The process becomes even simpler!

  • Daniel Fabi
    Daniel Fabi 7 months ago

    What if I want to have more than one dependent drop down?

  • Daniel Fabi
    Daniel Fabi 7 months ago

    Hi, what if I want to add another dependent dropdown?

  • Ushakar keerthi
    Ushakar keerthi 7 months ago +1

    Thank you very much. I got what I expected. Keep posting

  • Navi Sunner
    Navi Sunner 7 months ago

    thanks

  • Pratik Acharya
    Pratik Acharya 7 months ago

    Thanks, this really useful.
    I have another issue though, could you please suggest a way around?
    I have a cell which can have possible set of values (like a dropdown option) but this cell could have multiple values among the options. It can separate the values by a comma, or other delimiter. Is there a way user can select multiple values in a cell?

  • Mohamad Kanj
    Mohamad Kanj 7 months ago

    Awesome, Thank you so much for sharing this. So much appreciated!

  • Baraquiel Delgado
    Baraquiel Delgado 7 months ago

    Works only for one row

  • Mel Ellis
    Mel Ellis 7 months ago

    Very helpful but took way too long to get to the point, try trimming down your video lengths

  • Alex Paul
    Alex Paul 7 months ago

    Thank you! I've seen it done is with array formula function, but this seems to be more readable. Not as intuitive as Excel, but awesome either way!

  • Bill Parsons
    Bill Parsons 7 months ago

    Perfect, no music, clear speaking and intelligent! Click here to send him a new BMW

  • Puleen Gupta
    Puleen Gupta 7 months ago

    Thanks but if want to use in full A column because this technique will only will for A2(second row ) of Main sheet

  • SeongSik Moon
    SeongSik Moon 7 months ago

    Thanks!!

  • Jordan jc
    Jordan jc 7 months ago

    You helped me a lot !!! Thank you

  • Tapomoy Chakraborty
    Tapomoy Chakraborty 8 months ago +12

    The method doesn't work if I want to add another record

    • sangram tayade
      sangram tayade 5 months ago

      thexvid.com/video/1SIN5NyQ9fw/video.html

    • sangram tayade
      sangram tayade 5 months ago

      @UrTMazter Yes, even I need the same thing.

    • Anthony Hartono
      Anthony Hartono 5 months ago

      @UrTMazter does anyone know how to do it for multiple rows ?

    • UrTMazter
      UrTMazter 6 months ago +1

      Yeah, this is useless if you need to do this on multiple rows, all will point to the same list that the INDIRECT cell points to

  • Hoàng Yến Phan Lê

    Good training! Thank you!

  • Игорь Ильвутченко

    It's genious! Thank you a lot!

  • Gabriela Arreola
    Gabriela Arreola 8 months ago

    I get a #REF error when trying to use indirect formula. Any tips?

    • diskomiks
      diskomiks 8 months ago

      @Gabriela Arreola can you share how did you figure it out? i have the same issue. thanks.

    • Gabriela Arreola
      Gabriela Arreola 8 months ago +1

      Nevermind, figured it out. Thank you!

  • Tania Akter
    Tania Akter 8 months ago

    Thanks a lot sir!

  • Doyouvacay
    Doyouvacay 8 months ago

    Thanks so much, very helpful!

  • Maria Savina
    Maria Savina 9 months ago

    Thanks!!!!!

  • Vincent Paltep
    Vincent Paltep 9 months ago

    Thanks matey. Really helps

  • techeng13
    techeng13 10 months ago +1

    You are a good teacher, thanks

  • Laza Lazarevic
    Laza Lazarevic 11 months ago

    I have tried Data validation with Criteria is Custom (Query) formula. But can't seem to get the drop-down menu with the data from it. Any tips on that?

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  11 months ago

      It will not work as a custom formula. You'll have to use your function on a worksheet and then refer to that range for validation. Like in this video thexvid.com/video/j_0z4FReN5A/video.html

  • พิชิต แร่ถ่าย

    thank you

  • Diego Moura
    Diego Moura 11 months ago +2

    Muito bom.
    Você acaba de ajudar um Brasileiro :) thank you

  • Yun Kit (Samuel) CHAN
    Yun Kit (Samuel) CHAN 11 months ago

    Awesome

  • Late Goodbye
    Late Goodbye 11 months ago

    This channel is fantastic! Keep up the great work.
    Is it possible to get the drop down lists to work for IFS statements? Whenever I've tried the "Indirect" or "Filter" functions, it only display the top cell in my named ranged, not the entire list. Whereas if I only use an "IF" statement it pulls the entire named range. In my example, I want to alter what shoe sizes are available depending on the age and gender: =IFS(AND(MATCH(Age!B6,"Adult",0),MATCH(Gender!C6,"Male",0)),INDIRECT("ShoesMale")) - this only displays a single item
    =IF(AND(MATCH(Age!B6,"Adult",0),MATCH(Gender!C6,"Male",0)),INDIRECT("ShoesMale")) - this display the entire list but does not allow me to choose the options of Female, or whether the client is a child.
    Thanks in advance for any help you can provide!

    • Late Goodbye
      Late Goodbye 11 months ago

      Thanks! Just knowing that the INDIRECT function doesn't work with the IFS statement helped eliminate further time I was spending looking at that option. Using the FILTER option, as you recommended, helped me get the solution I needed. My formula is perhaps not the best way of doing it, as I had to refer to separate columns for Adults and Children, but I managed it get it work in the end. If anyone else is interested.
      =IF(AND(Age!B6="Adult",OR(Gender!C6="Male",Gender!C6="Female")),FILTER(H4:H,G4:G=Customer!C6),FILTER(J4:J,I4:I=Customer!B6))
      Thanks again @Learn Google Spreadsheets.

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  11 months ago

      No. But you can use filter function to get the results you want and then refer to that filtered range for data validation.

  • Austin Rykhus
    Austin Rykhus Year ago +1

    is it possible to eliminate an option from a dropdown after it is selected, I have several drop down lists, and I want to make sure I can't select the same item multiple times from a single list.

  • Wonkatonking
    Wonkatonking Year ago

    at 14:10 you slect BMW , and x3 .. > then go to toyota ... you get the error
    is there any way to make it go blank if your get a error because the x3 cant be a toyota ?

    • LucJenson
      LucJenson Year ago

      Use =IFERROR(YourFormula) I just discovered it today and oh my god it was a wonder to find.

  • Luke Biernbaum
    Luke Biernbaum Year ago

    Thank you so much this video helped a ton!!

  • ToMo PoWeR
    ToMo PoWeR Year ago

    CHEERS PAL!

  • Vishal Thapa
    Vishal Thapa Year ago

    I want to add multiple items in one cell using data validation drop downs, something like this: thexvid.com/video/AUM6s-hbX6w/video.html