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