Quickly Create Multiple Dependent Drop-Down Lists in Microsoft Excel

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  quickly and easily create a multiple   dependent drop-down list in microsoft excel let  me give you a little example of what this means   because that can be confusing if you've never  heard of it before but it's actually something   very simple you can create now when i go  over to here you can see that there's fruit   and apple there's a drop down here so if i  drop down you see the four different fruit   that i have here it's pulling it's dependent  upon what is right here so if i go and choose   oranges on this one then what happens is if i go  ahead and drop down on this one it is the orange   list that comes up because it was dependent upon  this first one so i could pick that now if i want   to see how many i have and i drop down you can see  the 73 comes up because it was dependent upon that   and if i look underneath you can see in my  quantity here i have 73 so these were dependent   upon each other they dynamically changed based  on what i chose so that's what i want to go over   today is how to create that independent  dependent drop down list in microsoft excel

if you want to follow along today with what i'm  doing with the practice sheet just take a look   down below in the description click on it open  it up and download it as a copy to your computer   and then you'll be able to follow along the next  thing i just want to mention is that there are   many ways that you can create a multiple dependent  drop-down list i think this is one of the easiest   ways to do it and quickest but it does have its  drawbacks and as i create my drop-down list i'm   going to show you where the problems are how i can  fix it uh but then i'll put out other videos too   on different ways you can do this drop-down  multiple dependent drop-down lists also   so the first thing what i like to do uh with  my data and this is my data it's kind of   just stuff i threw together and the numbers  don't mean anything just quantity down below   but i like to make a table out of this information  and it's very quick to do the reason why i like to   make a table it allows me if i add anything later  on it dynamically updates with those so if i added   another banana it would uh automatically update my  list so that's why i'm going to make this a table   at first and i can do this by just going insert  table or on as a shortcut hit control t and this   pops open and you can mine does have the headers  and i'm going to hit ok and now it's a table and   i'm just going to leave it as this formatting i'm  not going to bother turning the bottom one into   a table for this example here but i'm going to  do this one now the next thing what i'll need   to do is actually name these list here or kind  of a range and i'm going to just kind of tell   you why here because when we use the direct  indirect function that i'm going to show you   it can pull out an address from somewhere when  we name a range it allows us that range becomes   a place that we can refer to so in this case it's  easy to do this i'm going to highlight this again   and i'm going to go up to the formulas and take  a look under create from selection this is where   we create names and so it's going to be based  on the top row so it's going to create these   four different uh regions in here named so i'm  going to hit okay now i want to show you where   this exists so if i click off of this and now i  drop down you can see i have table three oranges   lemons and bananas if i click on bananas it takes  me to this right here so this is the kind of that   named range now the indirect function that we're  going to use so i'll just give it a little example   i'm going to type a1 right here if i go and  start a formula just with the equal sign and   start typing indirect and here's my indirect i'm  just going to double click on this it can go get   kind of an address i can use this right here a1 is  actually referring to up here so if i click here   and just close this up and hit enter notice that  it says fruit form it doesn't say a1 because this   when i said indirect this is an address to here so  i'm going to be able to do that with the list so   if i'm in a certain spot i can go get a  list to come up so let's give this a try   the first drop down list that we want to create is  dependent upon these four headings right up here   uh so at this point these don't have to be uh  named ranges in this case this is just going   to be a selection so this is going to be data  validation that we use and if i go to insert here   oops sorry data and data validation  and just click on that you can see right here we

want to create a list so it's going to be a drop  down list if i go and drop down to the list one   what is the source that i want i'm going to just  click on this arrow here and the source is going   to be these four things and i'm just going  to hit enter and hit ok now take a look at   this i can go ahead and drop down just like  so i can drop down and everything's working   right now just like that now i'm going to go  back if i wanted to make any changes to this   i can go back and hit data validation and here  it is here and i can make any changes like this   i could uh also if i wanted to change it  from what i wanted up here as you can see   i'm going to hit ok so that takes care of the  first step now the next thing what i want to do   is uh have a drop down base on one of the list  the ranges that i created so what do i need to do   i need to use data validation again so i'm going  to go to a backup tier data validation and my list   and this is where i'm going to put my function my  formula so equals indirect here   just like that and where do i want so i could  go and click on it automatically puts it to   kind of absolute value of the dollar signs i don't  really want this because i want to be able to copy   this down so where is it going to be drawing  taking the information dependent upon what i   choose well it says lemons now but i can change  that so at this point here this is a six so i'm   just going to go and put a6 in brackets oops  i gotta make sure i put six there and close it   up hit okay so now if i go and choose let's say  apples drop down notice i get the list of apples   here if i choose uh bananas now that dynamically  changes depending upon what i chose here   drop down in here are all the bananas that i chose  now the last thing what i want to do is in this   example is add the amount that are down here so  what i'm going to do is just highlight everything   and i'm not going to make this a table so i'm just  going to go ahead and do the same thing what i did   before with the formulas and create from selection  a name so top row hit ok just like that notice it   if i drop down now look at all the different  ones that come up because these are all name   ranges not in a table now the problem is now  this is where you might not want to use this i   left this in because i knew mistakes would  happen see where it says ladyfinger or   granny smith when you do these names  like this they can't have spaces in   them or they can't just be a number so  this is going to be a problem for here   so this is why you might not want to use this  and i can show kind of how you can correct this   but then it's going to change different places in  here that you can't use the spaces either so let's   go here and just use our same way we go up to data  and data validation again i'm going to drop down   to my list and at this point so what is going  to be my source so i'm going to use the indirect   again so i'm going to type in indirect indirect  just like that and what's the spot i want to   take it from well i want to take it from b6  so i'm just going to put b6 just like this   and notice that it's saying uh some yeah  errors i'm going to hit yes here so if i go   hit apples and then i'm going to pick i'm  just going to pick fuji and drop down 67.    well that matches that's fine that's working  so that's exactly what we want to have happen   and at this point too i can copy this down and  then make different changes of this was bananas   and i could drop down to the bananas and 97  so that was and that's working now i'm going   to show you where this doesn't work so if i'm  going to go back to apple take a look if i pick   granny smith here i can't do anything so right  away this is the problem that i want to show you   that's happening here with this and i want to  show you how you could correct it but then again   you might want to use a different way because it  could change up everything how you have it listed   before and that make a lot of work now before  i show you how to do this fix i just want to

point out how quickly changes can be made and how  dynamic see how ladyfinger i'm missing the last   letter i didn't notice that before but if i go in  so the drop down if i drop down i uh and look for   oh i'm under the wrong one so under bananas here  drop down and it doesn't have an e but if i go   over here and just put my r in here if i was  going to drop down notice it's fixed now again   if i wanted to add something new i'll just type  new and i'll go back to bananas again and there's   new so it updates very quickly and that's why i  wanted to point out why we'd want to use a table   it allows that dynamically dynamic to update like  that so let's fix this problem now the problem is   when i pick granny smith here then it looks  and i said that the problem is going to   be this space so i'm going to have to make  some changes in a couple of different spots   so i would need to make this because it needs to  be identified uh same naming here right so granny   smith here and then granny smith here but then i  also have to make this a new name this again and   it's going to be without the space so if i go  back up to here and i'm just going to click on   my formulas and just go to here hit ok and now if  i do the drop down i should have a granny smith   you notice i have 2 here if i go back up to here  and drop down i have apples and a granny smith   and drop down 64. so that fixed it i'd have to  do the same thing to ladyfinger it's going to be   needing to be corrected in both spots here and  then i'm going to have to rename it so it gets   identified as a different range and named  range that will match what they pick so i hope   that makes sense about the correcting of it so if  you're planning to set this up thinking about your   naming conventions if it does work to do it this  way like i said there are different ways let me   know if this way works for you or if you need to  use a different method for your multiple dependent   drop down list i hope you like this example here  today if in microsoft excel let me know thanks   for watching this time on teachers tech i'll see  you next week with more tech tips and tutorials