Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells

let's take a look at creating smart dependent drop-down lists so there are two challenges that were faced with here number one is that the list for the dependent drop-down should expand automatically as new data is added to our source data and number two is that the dependent lists have different lengths so we want to make sure that our dependent list is compressed to just show the values so there shouldn't be any empty fields below in that list these are actually two common questions I get when people watch my drop-down list playlist we're gonna address both right now I'm going to show you two different versions on how you can get this done the first version uses the offset formula and the second version uses table referencing our sample data is this we have different divisions productivity games and utility and we have different apps that belong to that division let's start off with offset first as a first step I need to have my main drop-down that's basically this list here so all I'm gonna do is go to data go to data validation select list as the source that's going to be equal to these three cells here and I have my productivity games and utility so now comes the difficult part we need to find a formula that we can plug into the data validation box here that's gonna give us the effect that we're going for so basically it's going to restrict our list to the values it sees here now one formula that can help with that is the offset formula so as the first step I'm just going to write the formula right here because it's difficult to write it inside the data validation we don't get any help with the excel arguments that we need there and once we're happy with it once we see that it works I'm gonna plug it in to the data validation now offset can be a little bit confusing to get the hang of that first so I do recommend back you watch my offset video that just explains the basic of this formula first if you're not familiar with offset yet and if you are familiar with it let's get started first thing I need is a starting point right so just see it like you want to move on a map you when it gets somewhere so in this case my starting point I'll go with this one right so it just has to be close to your data normally it's at the beginning of your data next argument I need here is how many rows do I want to move down well I don't want productivity included in my second drop-down I just want to show the apps that are inside the productivity division so I do want to move down one cell right because I want to start right here next is how many columns do I want to move well that really depends on what the first drop-down says right so if the user selected productivity I don't want to move any columns I want to stay there if they selected games I do want to move one column right so here the match formula can come in handy why because the match formula returns a number right so it returns an address it tells offset how much to move first argument in match is the lookup value what are we looking for this one right so I'm gonna fix it with the f4 key next is where is it looking this up well there are right here let's fix it with f4 and the last argument is if we're looking for an exact match or not in this case we are so I'm gonna put a 0 in there okay so that's how much we want to move but there is something that we need to take care of because match starts to count from here right so if it finds productivity it actually returns a 1 right so let's just double check that I'm just gonna highlight this and press f9 and we see it's the one so right now what's going to happen is that we're actually going to move one this way we don't want that we want to stay there so we actually have to make a correction by deducting one from our match result now next argument is the height and that's the important one right because that's something we need dynamic we want to restrict our results to the number of apps that we have listed here right so here we need some type of dynamic formula but let's just leave it out for now let's just make sure that everything else works fine and then let's come back and revise this so for height I'm just gonna pick one right now next is going to be the width so how many columns we want to include it well we just want to include one column in there right so I'm just gonna put one or we can skip that argument it's an optional argument and let's close the bracket here just to make sure our offset works and we are in the same column so for productivity we get one cap for games we get fighter so that works fine now check this out if I change the one here so that was the height argument if I change it to let's say six and I press ENTER Excel it just shows me fighter here in the cell because you can't put six results in one cell but I can actually check what's behind this so if I click right here and click on f9 I can see six apps here now the challenge is to get the six to be dynamic because I want it to be as many apps as I have under games right but if I'm in productivity I need that list to be longer so I'm just going to press ctrl Z to go back here now let's go back and revise this six and see how we can make it dynamic well one formula that comes in handy here is the count a formula because we can count how many cells have text in them right so remember there is a difference between the count formula and account a formula the count formula only counts cells that have numbers in them and count a doesn't care if they have numbers or text it counts everything so in this case I'm gonna use count a but I can't just reference my cells to be this right I can't do that because sometimes I want them to be the a column sometimes B and sometimes C column so exactly this part of count a is something that we need to make variable and here again the offset formula comes into play so again let's start off with our reference which is our starting point that's going to be the same I'm gonna pick this one press f4 to now how many rows do I want to move down well I do want to still go down one row how many columns do I want to move that's the same as I had before so that's basically exactly this formula here so I'm just gonna copy and paste it right here now comes the height argument right so I'm actually dealing with the height argument inside the original height argument and right now I'm actually gonna fix that I'm gonna give it a much bigger height than I expect to have in my table just to make sure that I do include additional cells below this so if new data come in and new apps come in they're gonna get reflected in my result right so I'm gonna actually fix that to a bigger number and I'm gonna go with 20 in this case but you just have to adjust this for your own needs okay so in this case we're fixing it the width we just want one column so I'm just going to put a one in there okay so this bracket closes the offset formula and now I'm gonna close off my count a formula and press Enter okay so now let's check this out let's just highlight that count a part so the original height argument of offset and let's click on f9 to see what we get we have 12 so under games I must have 12 cells in there so let's just press escape to leave I'm gonna highlight this and the count is 12 I can see on the bottom of the count is 12 okay so this looks good let's just change this to productivity I can check the counts but what I can also do is click here inside the formula box and click on f9 and I can see everything listed here until the last up here so I'm gonna press escape to leave now I'm happy with this formula I'm gonna copy it ctrl C escape to leave now let's go back to data validation let's click on list and let's paste this formula okay so now let's check productivity so until here is fine let's add something new we can see it here let's switch to games perfect let's switch to utility this looks great as well let's just add something here just to double-check and that gets reflected in there okay so that just required a formula and then plugging in that formula inside the data validation box okay so now let's take a look at another way of doing this and that's using table referencing together with the index formula so what I've already done here is the first drop-down okay so that's for the parent a division now for the second drop-down what I'm going to do is to turn this data set into a table but not one table instead three separate tables yes I'm gonna click on productivity first ctrl shift down to highlight this area press ctrl T my date my table does have headers so I'm gonna say okay I'm just going to leave that design and change the name here table prod press ENTER I'm gonna repeat that for the other ones so control shift down control t enter table game table utility T okay so I have three different tables three different names now what I'm gonna do is to use the index formula to figure out the right table so let's do it here first and then let's copy and paste it in the data validation and now I'm gonna do something different I'm actually going to use the second type of referencing here for the index formula so I'm going to work with areas have three areas I have my productivity game and utility areas so what I'm gonna do is highlight them and I'm gonna hover over the table header here and click on this area I get the table name and then I get the table header notice if I put a comma here it thinks I'm moving to the next argument right but I actually don't want to move to the next argument here I want to add in this area and I want to add in this area ok I don't want to go to the column argument so what I need to do to stay in the same argument is to add these references in their own brackets right so to bring them together in the bracket and notice now when I move to the next argument and moving to the row argument these three references are now considered to be in the first argument for the row number how many rows do I want to move down well in this case I want to include all the rows right I don't want to move down any rows each single rule here should be included in my end result so I'm gonna skip that argument next is the column number how many columns do I want to move and here I just have one column for each area I don't want to move any columns inside that area so I'm just gonna skip that argument now comes how many areas do I want to move and that is given by my match formula just like the way we had it in the previous example we're gonna look productivity up f4 to fix it where are we looking this up we're looking it up right here I'm gonna press f4 to fix it and we want an exact match bracket close bracket close press ENTER okay it's giving us the first cell that it sees right here it's just because I'm copying and pasting this formula right in front of this right this is called implicit intersection if I actually paste it somewhere here we get value right so don't worry if in your case you're getting value if you click inside your formula and you press f9 you should see the full list behind that this looks good just don't press ENTER press ctrl-z or escape to leave okay so as the last step we said I want to copy and this inside the data validation right so let's try to do that I'm gonna go to data go to data validation click on list paste this in and click on OK I run into a problem because the data validation doesn't like this type of table referencing whenever you come across this limitation what you need to do is to paste this first inside name manager so just create a name out of this so go to formulas go to name manager these are the three different tables that I have here click on new give your formula and you name I'm just going to call it my list and paste in your formula right here and then click on OK and close ok so now go back to data validation go to list and let's put in the name which I forgot what it was so I'm gonna click on f3 that brings up my names it was my list and click on OK so now I get one cow to didactic and if I switch to games my list should be much shorter now let's add something to this so let's say new game new game to and it should get reflected in here ok so that's how you can create dependent drop-down lists that are not just dynamic but they also have different lengths I hope you liked this video if you did if you learned something new don't forget to give it a thumbs up and if you want to become more advanced in excel and you haven't subscribed to this channel consider subscribing [Music] you

Channel: Leila Gharani

Views: 391,398

Rating: 4.9312286 out of 5

Keywords: expandable drop down list, drop down exclude empty cells, excel drop down list, excel dependent drop down list index match, drop down without name ranges, drop down with offset, offset in data validation, drop down with excel tables, dynamic dropdown list excel, create data validation drop down excel, Excel Tutorials, Leila Gharani, Excel 2016, XelplusVis, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts

Id: VxReJslyJm8

Length: 15min 54sec (954 seconds)

Published: Thu Jul 19 2018