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

Instructions and Help about When Form 2220 Dependents

Okay so in this video we'll make a dependent drop-down list let me show you what I mean by that so I'm gonna go ahead and name this tab lists okay that took longer than I thought it would there we are so I'm gonna make a new tab I'm gonna call this main so this main tab what I want to do I want to have to drop down like this and the first drop down it's gonna be the mix so I want to be able to choose between different makes of cars so between Toyota BMW you know or whatever else is in here and have it drop down for that so once I choose let's say Toyota I want to be able to choose the model in the next drop down list but once I select Toyota in model drop down I should only have Toyota models but if I select BMW I should only have BMW models and so on and that's gonna be what we'll be making in this video now a couple of concepts we have to understand to be able to do this I'm gonna cover first and then get back to actually doing the drop down so the first thing we'll have to do will have to understand what's a named range and how we can actually use that so let's say I have some numbers here so if I try to sum up these numbers I'll do equal sum and then I'll sum up these numbers right here and I'm gonna get my total 126 right now another way to do the same thing I could just name this range so I'll highlight the range I'm planning to use and I'm gonna right click and then in my list of options I have define named range so I'm gonna click there and I'm gonna name this range so let's say I named this ket right so I'm gonna name this ket hit done and these are some other ranges that are named apparently there is an error in them but doesn't matter if you don't like them you could delete those that's what I'm gonna do so there it is I named that range catch and what I can do right now I can go back and do equal sum and then instead of highlighting that range from b3 through b7 just simply say catch just like that I'm gonna get the same 126 and that's what we call a named range so we can take a range in excel and give it a name and then use that name across our file but so if I have a different tab I could still use equal sum and catch and that's still going to work so you don't have to refer to the tab name then range you just type the name of the range and it will just refer to that range so that's the concept of named ranges and sometimes it's easier to just name something and refer to it rather than select and in some certain cases it's more beneficial than others but that's pretty much it that's one thing we need to know about named ranges the second thing we need to know to be able to make this dropdowns is a function called indirect so let me show you how that function works and what it does I'm gonna delete this because this doesn't exist anymore so there we are so now let's talk about indirect function I'm gonna go ahead and type some well let's just start with something hopefully easy so I'm gonna type 12 in B for cell and then I'm gonna go here and type B for all right so 12 here before here so let's actually just put this in some random cell so it's not in the same row not in the same column so this says 12 this says before and in B for cell we actually have 12 so what I can do I can use a function indirect then I can have the reference as a string so I'm gonna basically point this to a cell that has before in it and if I close that parenthesis see I'm referring to this cell that has before in it and I'm saying indirect before if I had enter that says 12 because indirect I take the before cell and as a result the before reference works and that's 12 and we get 12 as a result now indirect can also work with ranges so if I have multiple numbers here and I say from b3 to b6 something like that so you can see that my indirect automatically just looked at that range and did what it's supposed to do because now it's looking at the cell and it says from b3 through b6 which is this entire range of values now if I wanted to just sum them up I could say some indirect e7 and that should work just fine so that's our 94 total of all of these numbers okay so that's indirect function now similarly to how it before we can also use indirect function not only with the regular references but but named ranges so what I mean by that I can just highlight this cells and I'm gonna just give this a name so I'm gonna say I'm gonna call that numbs done so I named the range cold numbs now I'm gonna go here and type numbs and you can see I'm getting the same result because indirect looks at that numbs in the cell and then it looks at it as a cell reference rather than text and that ends up being this named range which has this range so it just works out just fine so that's what we need to be able to do this dynamic drop-down that we're going for so I'm gonna delete all