Video instructions and help with filling out and completing What Form 2220 Dependents

Instructions and Help about What Form 2220 Dependents

In this video I'm going to show you how to create a dependent drop-down list but our challenge here what is different to the previous videos is that our headers our lists are not sitting right beside each other but they're sitting right below each other so in a previous video I showed you how you can make a dependent drop-down list using the index function but our data was given to us in separate columns so we had a header and then we had the contents of that header and they were sitting right beside each other question came on how do you handle cases when your data is not sitting right beside each other but they're actually sitting right below each other and each of these has different sizes so some could be longer and some could be shorter so how could you create a dependent drop-down list with a data set like that the master data for our lists is structured in this way so that on the left hand side we have the division and on the right hand side we have the app names that belong to each division so we have three divisions here productivity game and utility the first drop-down list is this one where the user goes in and selects the division now this is something that I fix if you go to data data validation with lists I just typed it in productivity game utility because it's not likely that this will change but the second drop-down that's going to be dependent on this one I'm going to show you how to create this using the offset function so if you're not familiar with offset I have a video that describes the basics of this formulas if you feel like you're getting lost make sure you watch that video first I'm going to put the link to it in the descriptions below so offset is like a GPS if you're familiar with index the difference with it is that it doesn't need a predefined map on where it should move what you need to give offset is a starting point and then it can move anywhere you want it to move that it only makes sense that the starting point is close to your answer right so in this case our answer or answers are going to be here so it makes sense that the starting point is somewhere here it could be here it could be this one or it could be this one doesn't really matter what you select the only thing you need to think about is that depending on your starting point the other arguments that you choose here will be different in this case I'm going to start right here on the column header now the next argument is how many rows do I want to move down well that depends on this selection right so I want to move down all the way till I get to the first occurrence of that division which function could give me that right so let's think that Rose is a number right so I need a function that's going to return a number and the match function is great for that what do I need to look up well I need to look up this division where am I looking it up in here and I do want an exact match so the last argument has to be zero okay so let's just double-check this if I highlight and press f9 I get one I have productivity I move one cell down which is good the next argument is how many columns do I want to move I'm here right I'm starting here I don't want to move any columns if I was starting here yes I do need to move one column but right now I don't so that can be 0 then this one is the important one what's the height and height means how many cells do you want returned so if I put 1 1 as height and 1 as with it's going to return one cell so that's when Cal if I change this to game that's fighter rights the starting points looks right but I don't want to just return one cell I want to return many cells all the cells that are in front of game so I need to replace this one with a function that's going to return the number of cells that are in front of game what formula could I use for that countif function first argument is the range where I'm counting in so that's going to be the same one as before so that's going to be my division the next argument is the criteria so what am I looking for this okay I'm going to close bracket and that's that that's the one in the last argument that's an optional argument but we want the width to be one so we're just going to press Enter it looks like that we're getting an error but that's not always the case where you have formulas that are actually returning more than one cell right Excel can't put all of them in one cell so what we can do is just click in the formula box and press f9 and we can see it's returning fighter to Arcade that must be our games just going to press ctrl Z to go back let's just go check arcade that's the last one in the game so it looks good now all I need to do is to copy and paste this formula in the data validation box but before I do that let me just fix everything and copy go here where you want to have the data validation select list and paste right here okay so that's fighter till arcade that looks good let's switch to productivity that's when cow to didactic that looks good