Google Sheets - Create Multiple Dependent Drop-Down Lists

hi there jamie here today at teachers  tech hope you're having a great day today   today i want to show you how you can  create a multiple dependent drop-down list   in google sheets so what i mean by this is  if you have one cell you choose something   it's dependent the answer you get  will be dependent on what you choose   so in this case i have a drop down you can see i  have different fruit if i choose oranges now this   drop down was dependent upon what i chose so i see  a list of oranges and when i choose something here   you can see the amount remaining was dependent  upon what was in here so i can have multiple   places so if i choose apples drop down now i  have a list of apples and then i'm going to   have the amount left so that's what i want to  show you how to create at teachers tech today so if you want to follow along with this tutorial  i'll put a link to this spreadsheet down below   in the description and then all you have to do is  go file make a copy and then you'll have your own   copy in your google drive now first of all there's  a couple things that we're going to be working   with we'll be working with a function and we'll be  working with data validation so data validation is   the drop down that allows us to put the drop down  in the function and i just want to start talking   about the function what it is we're going to use  here and it is the indirect function and what the   indirect function does you kind of tell a place  you put an address in and it will display what   you put there so that could either be a cell or a  named range and i'll show you how to do that too   so for an example in a1 right here i have fruit  order form i'm just going to type a1 just like   that so it's like an address if i go ahead and  then type use this function and go equals indirect   so here it is right here indirect and i just  click on this cell and i'm just going to close it   and hit enter notice even though i clicked put it  told to go to this cell it put in this information   because that a1 was an address to this spot so  you can do it with a cell but you can also do   it with a range so in this case i have certain  list here that i'm going to make turn into named   ranges and that's what we're going to do next  so i just want to point out in this practice   spreadsheet that you have there's two tabs down  below we have sheet one which has this information   and then there's quantity what you're going to  be adding to this third column to pop up once we   choose the type now the first thing what we're  going to do is do the data validation on this   first one so what that means is there's going to  be a drop down and that drop-down is going to be   dependent upon these items right here at the fruit  and it's very quick to do this so if i go ahead   and just go i select where i want this drop-down  list to be i can go to data and then i just go to   data validation so at this point i already  selected where i wanted it to go so it's   sheet 1 a6 and that's where that's correct  criteria list from a range yes that's correct   enter arrange your formula what i'm going to do is  just select this i'll move this out of the way a   bit and i can highlight what i want so these four  right here so i hit okay and this looks all good   show drop down list and sell hit save and look at  this i have a drop down here that's dependent upon   these four items now let's say if i wanted  to change it i can just go back up to data   data validation again and maybe i knew i was  going to add more so if i go ahead and select   and i'm just going to go to the blank spot right  here hit okay so i'm just going to hit save   doesn't look like anything changed here but what  that allows me to do now is if i type pairs like   this drop down pairs is there so dynamically  updated as soon as i entered it i delete it   and then as i drop down it's gone again so  that's the first step so we have our first   uh drop down list dependent upon these  headings now what we want to have happen   is when we pick something like oranges we need  to have another drop-down list that gives us   these things right here so how do we do that well  first of all we are going to use the indirect   function but what we need to do is do some named  ranges and to do a named range what we're going   to do we're going to have to call them exactly  the same of the headers here that matches this   so when someone chooses apples is going to look  for a named range called apples so what i need to

do is i'm going to start with just highlighting  the different apples that i need to have in the   range i'm going to just highlight these three to  begin with go up to data and go to named ranges   when i click on it you can see this pops open  this is where i name it so i need to call this   apple's it needs to be spelt exactly the same as  what's going to be in the drop down the heading   so it's not going to work if you do a typo in  it i'm going to go ahead i can see the range   is correct i'm going to hit done i'm going to  add another range because i need to do bananas   this is going to be bananas these two right here  so i'm going to type bananas like so and hit done   now i'll just move over a bit more and we'll do  the last two here so i'll highlight oranges here   and this is uh just like so it has the right  thing highlighted hit done and my last one i need   a new range this is going to be called lemons  and highlight just here these three hit done   there's my different ranges that i have right now  now i wanted to point out with any of these i can   go back and edit if this closes down just go back  to data named ranges it opens up again and if i   was going to go to i'm going to hit edit on apples  let's say i knew that i was going to actually add   more apples well maybe i want to change the range  then i'm going to have the spots ready so i could   add some more to it so i'm selecting a larger area  so you could do that to each of these if you knew   the list were going to get longer so i'm going  to hit ok and done so what i need to do now is

using the indirect function i can't go right into  this cell yet i need to create a kind of a list   in order to redirect it into the spot with data  validation so normally i would put it way over   here kind of hide it or even on another sheet  but for this tutorial here i'm just going to   put it here so you can see kind of everything  at once and i'll put it right in this cell   here so i'm going to use the indirect equals and  i'm going to type in indirect here it is and i'm   going to reference a cell so if i reference this  cell right here a6 so what that means is whatever   it says so if it says lemons in this cell well we  have a range named lemon so it should give me the   uh that list back so i hit enter and there it is  if i go back and check and i'm like oh i made a   mistake what was my mistake or do i have lemons  something's up with my list i can tell because   that's not correct so if i go over to lemons  look here i have it wrong so if i go oranges   so i need to correct this so this is what i was  talking about before if i go back and pick my   range what is it i want i hit okay and hit done  and let's try this again make sure that we can see   lemons is actually selected now i go back and i'll  just change it uh bananas you can see it changes   if i go back to lemons there that's correct so  i made a change i could see something was wrong   now it always this indirect tells me that it  looks at this word is connected to   a named range and then it places it right  here where i told it but i want this to be   in a drop down so what i'm going to do at this  spot here is use my oops my data validation   go to data validation right here again and enter  my range i'm going to go here so what's my range   now sometimes what people do is just click the  first one and hit ok and hit save the problem with   this if i go at lemons and drop down notice it's  only giving me the first one i have these other   ones i'm going to go back and go to data data  validation again i need to make sure my range   is going to be long enough to cover everything  for all the lists too for the drop down so   i probably want to go extras i know this is plenty  for my example here tonight i hit ok hit save so   when i go back here i can see all of them now so  if i chose bananas this right here is reflecting   this and like what i said is i might i wouldn't  put this in kind of main site i would probably   hide it off where people aren't going to see it  or put it on a different sheet or hide that part   just but for today's i'm showing you so you can  see what happens when this changes uh here we get   our different list pops open and then it's  reflected in this list here so that's the first   two dependent drop downs i do want to do that one  more uh what i mentioned but what i want to point

out is with the quantity and as i talked about  this ladyfinger what's wrong with ladyfinger   well when i have to go through and create named  lists out of these so that part uh is easy so if i   go i can click in here i have my named ranges  still open i hit named range i can call this   fuji because i have to keep things named the  same so i now have fujis if i go i have to do   this to each the list but the problem is here with  ladyfinger you can't have any spaces in your named   ranges or you can't start it with a number or use  true or false in it so i would need to do to fix   this i could use an underscore here but that means  i also have to fix it here for it to be reflected   when it gets chosen so i'd have to go underscore  here and when i name it over here it's going to be   the same thing so i'm going to go through and you  i named the first one i'm going to go through and   just name this entire list by clicking on each  one so this one right here would be honeycrisp   like this and hit done so i'm going to go just  finish off this list and then i'll come back so   i finished off the list here and i have to make  a quick change to bananas that i saw when i was   re-watching that come up so i'm just gonna  go check my range make sure it's this here   hit okay and done so that should reflect now if i  have bananas there they are right there i can even   see the lady finger is coming in with the  uh underscore i didn't have to use underscore   i could have pushed it together and just made one  long word i just chose the uh underscore with it   so in this last spot all i really need is a just  indirect because i'm not doing a data validation   drop down i could do another drop down if  i wanted to but uh in this case i'm just gonna do   a indirect so i'll just start typing my function  in here it comes up and what is it that i want to   have so if they choose this i want it to say  the reflect depending upon the how many are left   of that so if i hit enter three if i go over to  quantity you can see there's three if i go and   change this number to 10 and go back it reflects  right away on it if you want to have another row

below it here you could copy this  one down so i could just pull down   and i'm going to get the drop down just like  before but what you'll need to do is create more   of these places here so we're going to have this  where am i going to be pulling it from because   this one right here remember uh is based on this  choice right here so if you do have a double one   so what i mean is if i just create another one  right beside it again i wouldn't do this on   a normal range just to keep things so you can  see it if i was using my equals indirect here   just like i showed you and where is it based on  this time well it's based on this one and hit   return so now as this one changes uh to something  else you can see it changes then this is where   i'd have to put my data validation in and you  can go back and select your spot that you uh   need hit okay and save it so now this is going to  reflect and then the next step would be the same   on that line so you can have those multiple  uh ones you just follow the steps that i did   before when we put this one this would just  again be that last one with indirect here and   it's going to be based on what they pick here hit  return and you're going to get 56 just like that   so you could have those multiple rows if you want  so i hope you like this way of using the multiple   dependent drop down list inside micros inside  google sheets i did do the other microsoft one   so if you're microsoft excel i'll put the link  to that one below and up in the card so thanks   for watching this week on teachers tech i'll see  you next time with more tech tips and tutorials