Video instructions and help with filling out and completing Can Form 8815 Dependent

Instructions and Help about Can Form 8815 Dependent

Okay so this is gonna be my next and hopefully the last video about dynamic dependent drop-down lists and if you did watch the other drop-down list videos you've probably seen how I made this project where I have this drop-down you select one of these brands and then you have basically the models that show up just for that particular brand so if I go here to this tab this is basically the way it's laid out we have this friends here in our C column and basically on the right we have the models and depending on which one of these makes we select so if we go with Ferrari we should only get models that show up over here so if I go back here and select this you're gonna see that the drop-down only has those models from that particular make now the challenge with this particular way of doing this is that this is only going to work if you just have two boxes side-by-side that you're trying to use this for but if you're trying to do this for the whole column so you want to have this whole column to work the same way when you select a brand then you have the models for that brand next to it this is not going to work and to make something like this we're gonna have to use app scripts now this is gonna be a pretty fun video for me because I haven't done this before but should be a problem using app scripts to get this done it is also probably gonna be a long video but if you know this channel you know that's not new it happens all the time anyway so let's go ahead and get started so the first thing I'm going to do I'm going to restructure this so I'm gonna clean this up I'm gonna first of all delete this first two columns here because we're not gonna need that anymore now I'm gonna also restructure this data the other way so it's probably nicer for the user so paste transposed like this I'm gonna delete this rows on top so now I should have the brands on top and the models in each column so now I'm gonna go back here first of all let me remove this data validation now here obviously we have this so I'm gonna actually remove that as well I'm gonna start over from scratch so this is where we are so what we need to do we need to first make the first data validation tab which is basically gonna be this column so I'm gonna do control shift down starting from the second row and top there then I'll do data data validation and then in here I'm gonna select list from range now here I'm gonna click on this little select data thing move this to the right go to the other tab and I'm gonna select this entire row going all the way to the right I'm gonna include blanks because I'm probably gonna be adding more going forward right so hit OK save and if I go back that should take care of the first column so if I go to the top you'll see how I only have makes from that particular brand basically I have the brand selection drop-down right now the challenging part is to do the model drop-down so that if you select let's say BMW now you should have a drop-down of just BMW brand going to the right so to do that we're going to use app scripts now I'm gonna go under tools script editor and we'll get started with app scripts so I'm gonna click here on title project let's give this a name so drop-down lists something it okay I should get us started now I'm not gonna go through every little things in Apps Script so if you're completely new to Apps Script it's gonna be difficult to follow this basically if you have been watching at least some of the Apps scripts videos you should be fine now if you haven't at least go back and watch the first couple of Apps scripts videos to have some idea what's going on right with Apps scripts now I'm gonna start first of all by accessing my spreadsheet app and my spreadsheet right here so I'll create a variable SS and I'm gonna say spreadsheet app dot and I'll go ahead and get the active spreadsheet get the active sheet in that spreadsheet and I should do it get active spreadsheet get active sheet so that's our spreadsheet so go ahead save this once in a while so what I'll need to do I'll need to basically show you how to do a couple of things so the first thing is we want some changes to happen when somebody changes something in here in this column of values so we can do that I'm gonna actually leave that top one alone and I'm gonna use this function call on edit and on added is a special function in spreadsheets and that's a function that gonna be triggered every time there's a change on the spreadsheet so for example if I just let me just copy this line put this here and then say SS dot get range and let's just do a basic Reince all to be to : let me actually use a different column d 2 : d 8 and i'll do that set value set value so and I'll set it to I don't $9.99 so just so that you see what's gonna happen right so I'm gonna save this now I think we may need to get access to the spreadsheet to get this work but I'm gonna try this anyways so I'm gonna go back to this drop-down and right now basically we're saying when something changes on our spreadsheet