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 • 97

  • Jhoni Rosales
    Jhoni Rosales 2 days ago

    Wow! Thank you

  • KALLAPPA DHANGAR
    KALLAPPA DHANGAR 14 days 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ı 24 days ago

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

  • Christopher Bacani
    Christopher Bacani Month ago

    How do you add rows?

  • Ageu Matheus
    Ageu Matheus 2 months ago

    Thanks!!!!!!

  • Steve Matta
    Steve Matta 2 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 2 months ago

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

  • omar khaled
    omar khaled 2 months ago +1

    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 2 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 3 months ago +9

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

    • demonfire691
      demonfire691 Month ago

      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.

    • Som Das
      Som Das Month ago +1

      hey do you got any solution about this ? Because i am also searching for the solution of the same.

  • Josh Lazo
    Josh Lazo 3 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  3 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 3 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 3 months ago

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

  • Daniel Fabi
    Daniel Fabi 3 months ago

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

  • Ushakar keerthi
    Ushakar keerthi 4 months ago

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

  • Navi Sunner
    Navi Sunner 4 months ago

    thanks

  • Pratik Acharya
    Pratik Acharya 4 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 4 months ago

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

  • Baraquiel Delgado
    Baraquiel Delgado 4 months ago

    Works only for one row

  • Mel Ellis
    Mel Ellis 4 months ago

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

  • Alex Paul
    Alex Paul 4 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 4 months ago

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

  • Puleen Gupta
    Puleen Gupta 4 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 4 months ago

    Thanks!!

  • ling jiachim
    ling jiachim 4 months ago

    You helped me a lot !!! Thank you

  • Tapomoy Chakraborty
    Tapomoy Chakraborty 5 months ago +11

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

    • sangram tayade
      sangram tayade 2 months ago

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

    • sangram tayade
      sangram tayade 2 months ago

      @UrTMazter Yes, even I need the same thing.

    • Anthony Hartono
      Anthony Hartono 2 months ago

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

    • UrTMazter
      UrTMazter 2 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 5 months ago

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

    • diskomiks
      diskomiks 5 months ago

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

    • Gabriela Arreola
      Gabriela Arreola 5 months ago +1

      Nevermind, figured it out. Thank you!

  • Tania Akter
    Tania Akter 5 months ago

    Thanks a lot sir!

  • Doyouvacay
    Doyouvacay 5 months ago

    Thanks so much, very helpful!

  • Maria Savina
    Maria Savina 6 months ago

    Thanks!!!!!

  • Vincent Paltep
    Vincent Paltep 6 months ago

    Thanks matey. Really helps

  • techeng13
    techeng13 7 months ago +1

    You are a good teacher, thanks

  • Laza Lazarevic
    Laza Lazarevic 8 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  8 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

  • Diego Moura
    Diego Moura 8 months ago +1

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

  • Yun Kit Samuel CHAN
    Yun Kit Samuel CHAN 8 months ago

    Awesome

  • Late Goodbye
    Late Goodbye 8 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 8 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  8 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 9 months 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.

  • billyboy402
    billyboy402 9 months 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 9 months ago

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

  • Luke Biernbaum
    Luke Biernbaum 9 months ago

    Thank you so much this video helped a ton!!

  • ToMo PoWeR
    ToMo PoWeR 9 months ago

    CHEERS PAL!

  • Vishal Thapa
    Vishal Thapa 9 months 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

  • Gabe Riley
    Gabe Riley 10 months ago +3

    Bless your soul. This is pure genius.

  • David Benaim
    David Benaim 10 months ago

    Can this be done using a FILTER function? So that its more dynamic & doesnt need named ranges?

  • Saret GNasoh
    Saret GNasoh 10 months ago

    wonder if you know for multiple dropdown list

  • Byunghun Yoo
    Byunghun Yoo 11 months ago

    You are a LIFE SAVER! thank you so much for putting this up.

  • Сергей MILITER
    Сергей MILITER 11 months ago +3

    Now make second similar row

  • Arsalan Ahmad
    Arsalan Ahmad 11 months ago

    Great video, please let me know the solution to my problem related to this technique. I am using your script for the same purpose you mentioned. I just need to use this script in all sheets of a spreadsheet and not only on whats named "Main" which means I don't want to give it a particular sheet's name to run this script on. Also I don't want the script to draw the lists from only sheet which is named "lists" and I want it to search in the same sheet where its running the script on. The places from where to look for data I can set, I just need help with the above 2. Thanks dude.

  • Ali Mulla
    Ali Mulla Year ago

    Thanks. That was useful

  • arman sesar
    arman sesar Year ago

    my problem is if you need multiple rows of models and makes you will need to dedicate an entire page to just Indirect functions to play out it doesn't make sense

  • Jorginho Pessoa
    Jorginho Pessoa Year ago

    Thank you so much!

  • Anshul Khandelwal

    This seems to work only for single word. If I have to validate for cases which have multiple words or special character, I can't use the indirect function because range name doesn't accept special characters!! Do you have a solution for this?

  • Lewisking50
    Lewisking50 Year ago

    Ranges can't contain spaces and underscores make everything look ugly. Anyone know a workaround for this?

  • Joey Hitchcock
    Joey Hitchcock Year ago

    I follow your instructions to the T but I get an error message when I try. I dont know why.

  • sartaj shaikh
    sartaj shaikh Year ago

    can i give regional names to name ranges in google sheets. Please give me some guidance

  • Mamdouh Thabet
    Mamdouh Thabet Year ago +8

    Is it possible to continue this validation down the column? So that if A3 is Audi, B3 will also only show the option available for Audi?

  • Benedetta Capuani

    Fantastic, this is the 4th video that I see about this and it is the easier and more understandable one. Thanks!

  • justfly2525
    justfly2525 Year ago +10

    INDIRECT: Awesome, I've needed that function so many times!

  • Shaelie Cook
    Shaelie Cook Year ago

    Is it possible to continue this validation down the column? So that if A3 is Audi, B3 will also only show the option available for Audi?

  • Jason Guo
    Jason Guo Year ago

    Great explanation!!!!!!!!

  • Pipe
    Pipe Year ago

    Hey great video! I have a question, I'm trying to do a drop-dpwn menu with dates. I want it to show Month-Year (MMM-YY) I put the format in in the list of months-years but when I set the drop down, it will show the list as "dd/mm/yyy". Can I change that somehow? The format of the date IN the drop down? Thnx!

  • Cao Huu Quang
    Cao Huu Quang Year ago

    Many thanks! But I have a ques! I want to have more cells like A2 B2 in A3 B3, A4 B4.... all of them use the same data in the Lists sheet. So how do make it?

    • Som Das
      Som Das Month ago

      @Learn Google Spreadsheets any other way without APP SCRIPTS ??

    • Cao Huu Quang
      Cao Huu Quang Year ago

      Learn Google Spreadsheets Thank you for your sharing! It is really helpful for me. It is exactly what I need. Thank you very much!!!! 🤗

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  Year ago

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

  • Minh Trí Hồ
    Minh Trí Hồ Year ago

    Great! Many thanks!

  • Elmez T.
    Elmez T. Year ago

    plus.google.com/u/0/+JuiloGonzalezT please contact me, I am from a Film Festival. I want to know how much it will cost us to automate some things in our Festival, and I think you are the perfect person to work with.

  • MAQ Guide
    MAQ Guide Year ago

    Nice..

  • Rocky Dagalea
    Rocky Dagalea Year ago

    can you help me please. I followed one of your video using Query function. I am having trouble combining two tabs into one master sheet because it have different headings. can you help me please.
    docs.google.com/spreadsheets/d/1UKrXKzeodcdWapIKb9O44kNZGuIzEkRmjmmTckvpPi4/edit?usp=sharing
    I want the data from week 1 & week 2 combined in Insert tab,

  • Tarun Makhija
    Tarun Makhija Year ago

    Is it possible to add the indirect formula in the data validation itself rather than calling it in a different range and then calling that range in the validation dropdown? It is possible in excel, haven't tried in Google sheets.

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  Year ago

      It won't accept indirect in validation directly.

    • Learn Google Spreadsheets
      Learn Google Spreadsheets  Year ago

      Not possible in Google Sheets unfortunately.

    • Tarun Makhija
      Tarun Makhija Year ago

      Btw, a small tip, if you want to keep the range open to additions later on, then u can write the range as (Starting cell:ending column) or (starting cell:ending row). If it's a column the (A1:A). If it's a row then (A1:1). This will define the whole column or row as ur range and you can keep adding values without worrying about going out of the range

  • Poom Laupattarakasem

    Thank youuuu

  • Laza Lazarevic
    Laza Lazarevic Year ago

    thanx man

  • Arkadiusz Lech
    Arkadiusz Lech Year ago

    So if you want to add another colummn for new brand do you have to define new range for that brand. Is it any way to deffine range automaticly or to use indirect function without deffining range. If so how to make it?