Google Sheets - Dependent Drop Down List for Entire Column, Multiple Levels

all right so since I had my last video about dependent drop downs I've had a lot of requests to create a dependent drop-down that will go for another level so basically what we're trying to do we're trying to create little drop-down validations lists so basically if I pick one thing in the first column I will only have some options in the second column and then if I pick some option in the second column I will have only certain options in the third column that's what I'm gonna try to do in this one so let me explain you what the logic is gonna be so for me I have this options tab and here I have all the options listed so the idea here is that the first column will be different regions so if we select for example Midwestern here in the first column we should only get states that are in Midwestern so if we look here we should get only Illinois here is one of them and Indiana is another one right now if we select Saturn then we're gonna get like this one and this one right here so then if we do Midwestern and Illinois in the second column right here then we want to just get representatives from those so the logic that I'm gonna use here is that if I structure my data like this in the options tab if I could just take this list and filter it and just filter it to whatever is selected in that first drop-down right here so if its Midwestern we'll filter it to Midwestern that way you get a list of states we should be selecting and then if we also select let's say Illinois here and we'll filter this column to say Illinois then we're gonna have just a list of sales reps that we need to have as a drop-down in the third column so that's the logic I'm gonna go with to structure this data basically just make it happen now we could have used filter function to do this if we were just trying to do this for one bro but I want this to work for the whole column so we're gonna have to use app scripts to make this happen so let's get started with a basic thing so let's make sure we just get a drop down here that will select the region from this column from our options tab so that's gonna be actually a very easy thing we don't need to do any app scripts for this all we have to do just select the area where we need to apply that validation I'm just gonna highlight some area here go on their data go data validation and then I'm gonna do lists from a range and click here in this box I want to reject any input beds anything different than what's selected I'm gonna click here go to my options tab and basically just select and I'm gonna select a lot more thinking that if we're adding more options here I just want to make sure we have those in the list as well I'm gonna hit OK save and that should take care of that part so as of right now I should have my first column of validation if I open this see I just have these another way Google sheets is gonna do it's gonna filter these out anyways so we're not gonna get every single one of this list we're just gonna get a unique list so basically here I only get a nice clean list of three regions so that works so the next level that we need to do we need to start using scripts this is where it's gonna get a little complicated so what I want to do here I want to make sure that this is selected like midwestern then this is gonna be a drop-down list of basically the options that match from here so for that I'm gonna go to my tools and script editor and we're gonna get started with our code I'm gonna zoom in a little bit save this and this project I'm gonna do DB VL so that's our drop-down validation really code anything you like obviously that's what I'm gonna call this so before we do anything with our logic here first of all let's figure out how do we just apply validation list with scripts let's say I want to apply some validation to Bisbee to sell right so in order for me to do this I'm gonna go to my script I'm gonna start with my spreadsheet app so first of all let me just read this worksheet this master worksheet to a variable and this options list to a variable I'm gonna go here for WS that's our worksheet and I'm gonna take my spreadsheet app and in this we're going to get our active spreadsheet which is the current spreadsheet that's open and I'm gonna get sheet by name so the worksheet I want to open is this master one in this particular case so that's the worksheet I want to work with so in this worksheet I want to be able to just apply some validation to this cell right here so in order for me to do that I'm gonna go to that worksheet I'm gonna get range and the range for now I'll just type the range like this so I'll say b2 so to that I'm gonna do set data validation and here we'll have to provide a rule so we need to create that rule so to create that rule I'm gonna go on top here and try to create that data validation rule so I'm gonna do a couple of things to make this happen the first thing I'm gonna make a nice array that's gonna be the list of validation all right so I'm gonna get an array here I'm gonna get a variable list and that's gonna be an array of some elements so I'm gonna go ahead and just type some values here I'll do a I'll do B I'll do gee and doesn't matter really a few things here and I want to apply that as the options for our validation so I'm gonna create a variable that will hold the validation rule that is gonna come from our spreadsheet app dot and we're gonna do this thing here cold let me see if I can find what it was cold new data validation there it is dot and here we have this require options and one of those is require value in lists which accepts an array so this value in list is gonna be in a ray and that array is gonna be this array right here so I'm gonna just say I don't know why I call this list should be list and then I'm gonna call this one list as well so that's the list I'm gonna provide dot and here we're gonna build that validation semicolon here I'm gonna say this now I could have left this like this but because this gets pretty long what I'm gonna do I'm gonna just send this to a new line like this and that is really exactly the same thing it's just going to be easier for me to be zoomed in and be able to type this and generally might be just easier to read later on so I'm gonna save this let's just apply this and see what happens right so I'm gonna run this function right here this is my function I'm gonna run that and let's just give it a second it will ask us for our regular permission stuff so I'm gonna open that gave necessary permissions here shouldn't be complicated so if you watched any of my previous videos you already know all the stuff so I apply this I go back let's go take a look what happened so we've applied this to be to cell let's go back B to C there is a list if I open it it says a B and G cool so now let's try to do something else here I want to show you something let's say I have a list that has some repeating items like this I'm gonna go ahead and try to run this to see what happens so I'm gonna run this let's go check it out I'm gonna open this and I get a B G same stuff we had before let's actually add something else here F and run that again I'm gonna go back and let's go check it out see we have this so basically what I'm trying to show you is that even if we have repeating items similar to when we applied this validation it's not gonna have all of those it's just gonna have a nice clean unique list which is great that means we don't have to worry about cleaning out our lists and making it you know unique lists it's just gonna happen automatically so one other thing I want to do so if you remember here what I did when I was setting this validation rule I made sure that I reject any other input that people type so what that means if I try to enter something different here see it basically gives me this error message so I can only have one of these three options now in this validation list if I just type this C it will let this go through it will just basically say it's invalid we just get the warning message so I want to make this the same way so it rejects the input so to do that I'm gonna go here after I do the require value in list option I'm gonna do another dot here and here I'm gonna do this option that says there it is set allow invalid so basically here we want to set if we do allow invalid options or not so I don't want to allow anything else so I'm gonna set this to false and this now should change it that when we applied the lists we cannot have any other items in the list so let me run this make sure it works go back here see we have the list let me type something else here in the list and we get an error message cool so so far so good so we were able to just figure out how we apply validation so what I'm gonna do right now I'm gonna just create a function to just apply this validation to a cell so I'm gonna create a function and I'm gonna say apply validation to cell so our regular functions syntax stuff goes here just a little zoom out here so we can see what's going on for right now what I'm gonna do I'm pretty much just gonna copy most of this stuff in here and then I'll show you what I'm gonna change in this one so what I want this function to accept is an ArrayList that's gonna be this list we can accept it so we can apply the list to that cell so I'm gonna remove that hard-coded list and just do an argument in this function what I also want to do I want to make sure that I give it a cell to which I'm applying this particular data validation so I'm gonna say cell and that cell is really gonna be this and because that's that we don't really need all of that stuff here so that's the way I'm gonna transform this function so basically we're gonna just create a rule using a list and the cell to which we want to apply this validation I'm gonna save it now the way I'm gonna use this function when I need to use it will have to instead of doing this we'll have a list and we'll have a cell so for example something like this so I'll say var cell equals to this and let me just apply two different cells so we verify that this actually works so let's do it for c2 and then I'm just gonna run that function right so I'm gonna copy this paste it here semicolon and that will accept is list from here and this cell from here so let's just verify and make sure it works I'm gonna run this my function thing go back and take a look awesome good so now we have a function to apply this validation so now I'm gonna leave all of this alone and move to something different for a second so I'm gonna do this function on edit this is a special function that runs every time you make a change on a spreadsheet so this function on added so basically every time you go here and type something in a cell or delete something from a cell or really maybe change this debt runs this function on add it automatically and that function actually accepts an argument and that argument is the event so we're gonna call it e short for that event and that event this available has a lot of different properties that we can actually use in our advantage so one of those properties that it has is this range property that's available which basically gives you like a range or basically a cell similar to just getting like this only that's gonna be the cell that was just modified so if I take that and get the value of that that'll basically give me the value that was in that cell that I was just in so similar to if I do this I can do dot get value right and that's gonna give me the cell that I mean so in addition to that get value we can also for example get get the column C right here get column there's gonna be like all of these methods should be available the same way they're available here so I'm gonna remove this let's just save this in a variable or cell equals to this I actually called it active cell so just to make it more specific so that's our active cell is that an active cell value would be the value in the current cell that were modifying so if I switch it to admit Western that will be Midwestern so what I'm gonna try to do I'm gonna try to do something like this I will take the value whatever you select here and try to put it here on the right now we have some validation here that may interfere with this so let me for now just clear that validation again go to our worksheet now we'll need to get this master worksheet again now because I might have to use this multiple times I'm just gonna make this global variable like this outside of the function so I can always refer to that worksheet and do things with that as necessary here what I'm gonna do I'm gonna take that worksheet and in that worksheet I'm gonna get range and the row is gonna be the second row and the column is the second column so that's two two and I'm gonna do set value so I'm gonna set the value of that cell to this which is the value of active cell so let's actually save this to a variable I'm gonna say var Val it's gonna be this and then I'm gonna take that variable and put that in that cell so I'm gonna save this let's just test and make sure this works go back here and try to change this to let's say Saturn and see we put that value here if I go here and change it to Western again we take whatever cell I was just modifying we take the value and put it in the cell even if I go to this cell and write and type something it's gonna put that value in the cell so right now I'm able to just grab the value from anywhere that was modified and basically just put it in the cell over here I'm gonna delete that and then it's gonna do the blank in that cell now the next step I want to do here I want to change a little bit so that it only works in a row that I'm in so right now it doesn't matter if I change it here wherever I change it applies to the same cell over here what I want to do right now is to apply things to the same row so if you change it in this row I want to apply in this row if you change it here I want to apply it here so in order for me to do that I will have to figure out which row it needs to be in and that we can again get from here remember that get row get column all that stuff we have available here so we can take that active cell so here instead of hard-coding it to the second bro I can say just active cell dot get row like this and that should get the row of the cell we were modifying so I'm gonna save this go back let's try to check what happened so I'm gonna go here and do Midwestern that does Midwestern here let's say I want Saturn that did Saturn in the same row as I was in like western again western in the same row but this is again it's gonna work wherever I am so if I go here and type something that's gonna go here on the left so I'm always now putting things in the second column no matter where the modification is so we might want to limit this to only happen when we change this in column a instead of making this happen no matter where you're making the change so I'm gonna go here and make a little extra changes here so instead of blindly just taking and doing this we may run an if statement to make sure that the column we're doing this changing is in the first column so to get the column actually I'm gonna move all the stuff to a variable this row I'm gonna copy that I'm gonna say var row actually just R is fine for row we'll do that I'm gonna do VAR c for column and we'll get the column for that one something like this and then we'll use this variable R here instead of doing that and here instead of just applying that I can first check to make sure that we are in the correct columns so I'm gonna do if and check if the column is equal to one then we'll do this thing that we do over here so I'm gonna tap this in save it so hopefully with this change we should only see this happens when we choose to drop down in the first column and we'll avoid changing the value when we are in different columns so I'm going to save this go back here and let's try this out so here if I change to midwestern that did it if I change to Western that did it and if I change it let's say over here or over here or over here see nothing happens now so we're able to limit that to just the first column that's where this whole thing happens so if you change values in the first column that's where we do this now the other thing I want to make sure is that this doesn't happen when I change it here so right now if I change this region to let's say region 1 it's still gonna go here and put region 1 because I limit it to the whole column but we probably don't want it to happen when we're in the first row I'm gonna add an extra condition here I'm gonna say and if the row is greater than 1 only then do we want to apply this so we'll do try to undo this like that so now let's try this out hopefully now it only works wherever it's supposed to so if I do region 1 nothing if I go back here and switch to that that works and if we do it here that doesn't do anything cool so so far we're able to get to this so we're able to just react on our selection from this column so one more thing I want to make sure is this so if I add another tab here and go here let's say and type something let's go back and see what happened here see it still did this so if I even go to a different worksheet and change the value in that range it's still modifying our spreadsheet over here I'm gonna delete that I guess I'll keep this just for testing so we also want to make sure this only happens when we're modifying stuff from this worksheet so to do that I will have to make sure I get the worksheet name that we're on so if we look at this range object let's see if we can get the worksheet see it says get sheet so we can do that and then we can get hopefully the name of that yeah and that would be the name of the worksheet so I don't want to do it here though I'm gonna cut that if I take that active cell again and check what the worksheet is let's make a variable WS name so that's gonna be this from that active cell which would hopefully get us which tab we're doing this on and then what we'll do in our if statement will say if that's the worksheet and actually I'm gonna do this first I'll say if the WS name equals two and that's gonna be the worksheet we want to do and so the worksheet I'm targeting is I believe it was called master there it is so I'm gonna check if we are in master tab and we're in that range only then let's actually do this so save this let's go back so first of all let's make sure it works here I'm gonna do this that worked I'm gonna go here and type some things and nothing good so I'm going to delete that that will so far clean that up that's good now the next thing we want to do we want to make sure that we don't just put whatever is here to here we wanted to just go grab the appropriate list for validation and apply it to the cell on the right to be able to do that we have to read this optional list and grab whatever's in here to an array so let's go back here and that's what I'm gonna do next let's create a variable for that worksheet so I'm gonna say WS options so that's gonna be our options worksheet and that tab is called options so it's that I want to read this entire range here to an array so that's gonna start from a2 through C all the way down so to do that we'll take that WS options worksheet and basically do get range from that and we have to start that range from the second row first column so that will be 2 1 and the number of rows we need to go down it's gonna be the last row is 12 minus 1 because we have 11 options the first one is the header so it's always gonna be the number of rows minus 1 to include the header so I'm gonna take that options worksheet dot get last row that should get us the last row we're gonna take that and do minus 1 to get to that 11 and finally the number of columns I want is 3 and I'm gonna do get values to read that entire thing to an array which will save to a variable will say var options so that will read it to an options a right that should be basically all of this so what we'll need to do we'll need to get through this list and filter it to whatever is selected here so to do that I have this options array and what I can do here this Val is gonna be the value that we grab from here whatever selected which is great so here I'm going to take that options array and I'm going to filter that array and that because it's an array will have this filter method on this and we're gonna do a callback function for this filter method and I'll do all four options I guess and we're gonna return so what I want to return is everything that's matching the value in the first column here in this array so the first column is gonna be the index 0 in my array so I'm gonna say let's get that back if the first index in that Oh which is the first column is equal to whatever the value is in there so that's the vaulting so that and we'll save this to variable again so that's filtered options so that will just filter hopefully if we do everything correctly to basically an array like this only it's not gonna have these other options it will just be lets say Midwestern options but we don't want this whole array we just need a list from that second column after we filter it and to do that we'll just map that filtered options to another array so we'll do var list to apply so that's going to be that filtered options and we'll map that a right again that will accept a callback function then I'll do again all short for each option and I'll return things from the second column which is index 0 1 which is Oh one here that will be my list to apply as a validation so what I want to do now is do pretty much this apply validation to self so I'm gonna use that function over here so the cell in this case is gonna be basically this thing right here so if I wanted to I can save it to a variable first so I'm gonna say var cell equals to this that will be the cell and the list will be this list to apply hopefully I got that right so I'm gonna save this and let's test this to make sure I did this correctly I'm gonna go back here and go to master and try this out let's go here and change it to let's say midwestern so that did not work so that didn't set our validation we'll have to see what we did wrong in this I'm gonna try to log the list to see if I'm getting a valid list let's hope logger works in this case because sometimes when you work with this events it doesn't function the way you would hope it does but I'm gonna test that out anyway so Saturn here I'll go here and try to see if I have anything in my log I got nothing so that's what I was worried about let's see if we can do console.log instead of doing logger.log probably likely to have the same issue here yep so it's probably not gonna work hold a second maybe it did work so let's see cannot find function filter in object midwestern all right so something is wrong so that did not work I can see why instead of doing get values here I did get value this should be in a right we're just getting one value so I'm gonna save that interestingly enough apparently console.log did work in this case so let's do Midwestern okay so that was good so we got this so that achieved our first goal so right now if I select something here we get this validation applied which are basically the states that match so if I go under western here we'll see in list in Western we should get see these two states which is exactly what we get right now when I change to that option one problem we're gonna have here if I go back here and switch this Midwestern yeah that actually works pretty well right but if I clear this what happens now hey that's nice that actually cleared that what if I already have something in that cell that's gonna be a problem see if I go and just clear the cell then we already have something here then I want to make sure that we also clear this thing so that means we have to check if the value of the cell basically is blank then we need to just clean whatever is in this cell as well so let's try to handle this so I'm gonna go here and check in here so before we do all of this validation stuff let's also do another if statement here and confirm that we have a value that's not blank so we'll say if Val is equal to basically this nothing let's try strictly cool suits see what happens here then we want to just set the other cell which is this cell dot clear content so let's try this to see what happened I'm gonna save this go back and check this out so we have this let's just try this Midwestern right this gives us this we select something here now we go to the cell and we just clear it and that clears this one too we might decide to clean this drop-down in that case and that might be a good idea because we will then escape doing all of this so I guess I'm gonna do that I'm gonna do an else statement here and only run this when we need to do that validation but that also means that now I have to make sure that here I also clear the validation from that cell so I'm gonna clear the contents of that cell and we're gonna also let's see clear validations so let's see if that's enough to take care of that so I'm gonna save that go back so now if I'm here and I clear that that also clears the validation if I select something that gives us our drop-down we have this if we go and change this to Midwestern AHA probably when we change that this should also clear the cell as well so every time we select something here we probably want to just clear this because it's not matching our validation anymore okay so let's do that so every time we select something from that list well this is going to be when it's blank even when it's not blank we want to clear the contents of that cell on the right first which is gonna happen here save that let's go back and test this so I do this midwestern I'm gonna get my validation I'm gonna select this let's say we switch it to Saturn that's going to clear that nice so we have these two I select this one so should be correct now and to sell that error was coming from this other sales so let's just clear that really quickly here so we don't have to deal with it so nice clean list here so Saturn we have these validations now so if I go to Western that should clean it up it's gonna go here and if I clear it it will just clear the validation from the cell okay so this is something that works for our first two columns now we need to make this apply to this third column as well so first I'm gonna do a few little changes to our existing code the first thing I want to do I want it to be easier to change which column is the main column that we want to work with so if we decide to change this column from column one two let's say column to this region column then we don't have to do tons of changes to our code and it's easier to make any changes if necessary okay so to make this changes first I'm gonna create a variable for our columns where we want to check for validations so I'm gonna say first level column that's gonna be the first column where we're checking for this so in this case its column one then I'm just gonna use that in my code so I'm gonna copy that so the column that we're checking this in remember was over here so we're checking in column one now if we decide to change it that's not gonna be one anymore so I'm gonna paste that here so that's gonna be whatever we use here in this first level column I'm gonna also make sure I do this for my second level column so I'll say second level column and that will be two in this case that's where we apply our validation in the second column so I'm going to copy that and I'm gonna change all of these tools here to that variable two so that should go so this should go I don't want this console.log any more here so I think that cleans that up as much as we need or at least it's pretty close let me do a couple of notes here so this is closing our F function here so I'll do closed if now hopefully take this to the next level I'm gonna also try to move this entire thing from here to its own function so I'm gonna create another function here I'm gonna say function apply first level validation and then I'm gonna go here and grab this entire thing from here and move it to this function let's see what parameters will have to pass for this to work we'll probably have to pass this value we'll have to pass this are the row we'll have to pass the column most likely where is the column well maybe we don't need to call so we'll see I'm gonna save that let's try this so I'm gonna do this apply validation function here let me see if this still works so I'm gonna save this go back go here so like something that gives us the list we clear this that clears it so seems like that still works so good so we're able to just move it to its own function so this just keeps this clean and now we can now move on to the next one so this was if we do it for this first level column now let's do an else if sort of statement I don't think I need this anymore because I cleaned it up very well let's just do and on edit here so we know what that thing is for and here I'm gonna add an else if statement so another condition we need to check for and the condition I'm going to it's pretty much largely gonna be very similar so we're gonna check for all of that stuff again we need to make sure this should be typed correctly first of all we need to make sure it's the master tab we need to make sure the column this time is not that column now we'll be checking if the column is the second-level column so and the row is bigger than one then we want to apply this now because I'm repeating this master master master we probably want to also move that to a variable will save our main ws name something like that equals two and we'll make it master and then we'll use that variable name in here so that's this and that's that I'm gonna change this to strict equals so basically now if we were to apply this to a different tab we only have to change it here so I'm gonna remove this function because we are not really using that one anyways and by the way since we're gonna do this we're using that master here on top as well so I'm gonna move that on top here and use that variable name right there maybe we should change this to a variable as well I'm actually gonna do that WS name equals two it's gonna be this thing and that's what we're gonna use right here so these are just things that are gonna make our lives easier when we try to make changes to this that's good so so far we got all of that together we figured out the first level now we have to do the second level I'm pretty much probably gonna do largely the same so I'm gonna do this and pass this now we're gonna make it and function I'm gonna say apply second-level validation and basically I'm just gonna for now I just copy this thing and see what we're gonna have to do with it for this to work right so I'm gonna do apply second-level validation so remember now what I'm trying to do for the first level it was if we choose something here we apply our validation here now we need to do this next one when we choose state here we will have only certain sales reps to choose from in the start column so now we're going to go to this second-level validation function and in this function first we'll check if that is blank so if it's blank we want to pretty much go and remove the content in here and for that that means we have to make the variable for that VAR third level column and that will be column 3 I'm gonna copy that move down and do my changes so if the value is blank which is now is gonna be the value in the second column then we want to set that third level column we want to clear the contents we want to clear any data validation and then if it's not blank then we're gonna do all of this so again we're gonna go and get the range we want to clear the contents again from that third level column from the third column then we're gonna go to filter options and filter it we need to filter it to our value from our current cell which is gonna be whatever selected here but we also have to make sure we filter it to a region as well now we have to filter this array right here by two columns now remember the value we're gonna get is just the value that comes from the second column we need to still figure out a way to get the value from the first column to see what we're filtering to and that what we're gonna do here we need to get the value well we have the row so since we have the row we should be able to get the value from their first column as well let's go ahead and do that so let's create a variable first level call value that should be equal to we'll go to our worksheet and get range and the row is going to be the row that we're in are and the column is gonna be well that column for the first level will use the variable here so that and we're gonna do that get value to get the value that is in that cell so that should get us whatever is stored here okay so now let's appropriately filter a list so when we filter our list we want to make sure that this first column is whatever is in that first column so that's the zero position and now it's not gonna be Val it's gonna be whatever that value is in that first column Dad and at the same time we want to make sure that whatever is in our second column which is this one which is our one in this case is equal to whatever the value is that selected over here which is gonna be that vowel that we already had which is the value from the current cell that was just modified so that should hopefully be able to filter our rate this is not our by the way this should be au which is the same as this one and then we're gonna need everything from which column here so we're gonna need everything from sales rep column which is 0-1 to second position so instead of getting everything from the first position that will be the second position and then once we got that list we want to apply we're gonna take the cell the cell is gonna be the worksheet we're gonna get range that's the current row now we want to apply not in the second-level column we want to apply it to our third level columns so we'll do our third level column and that will be our cell and then we'll apply the list and the cell the rest I believe should be the same let's try it so I'm gonna go to master go here select Saturn we got our list click ga we got validation we got these two let's go check if that's correct pull and Dale that's correct that works just fine now let's change this to this clears we got this that is correct good let's clear it out let's see what happens that works fine let's change this now I have a feeling that there's one thing we'll have to change is when we select like this let's say right now it works if I just go and clear this one then it's going to clear this one right but if I select this for example and I select the sales rep and then go here and change the first one or delete the first one see it only clears this one but we possibly also want to clear this one because now the whole thing was changed so we want to make sure we go back and change this first one so it clears not only this but also this one as well so let's go back and that was our first level validation so when the cell is blank not only we want to clear this one and clear the validation from that one we also want to clear those validations from that third level column so if I just copy and paste that over here and switch it with our third level column scroll down where we are here and here save this thing let's go back and check out what happened I'm gonna go back here try this again so we have this we select let's say we go back and select this that did not work I go back and see what I did wrong there Oh so I it's not like I did something wrong is that I this was the case when we're clearing the cell so if I clear the cell it will probably work but we also need to handle it here when there is a validation so we're gonna have to do this thing over here too for our third level column or probably all kinds of more fancier ways we can change this whole thing and rewrite it so that this now can be scaled to any number of columns but I'm not gonna worry about it to keep this as simple as possible for this so let's go here and do this Saturn that we got our sales rep we got our sales rep cool now I'll go here and change to Midwestern that all cleared the cell but seems like the validations are still there so we probably need to make sure we clear the validation from here as well so that's where we did this one so we need to also make sure we clear the contents and we also clear the validation rule from that third column we didn't really have to clear for the second column because we kind of applied a validation anyway maybe it was a good idea to clear that too just in case so I'm actually gonna do it second and third level columns okay let's check that this change this good would clear debt clears all of them we do this that applies here video this applies here we can't select the person we can delete it from there and that should just work out we have this validation and finally lastly let's make sure this works when we add more options to our options lists right so let's test this out so I'm gonna go back and add a new region for example let's call this one new region and that new region is gonna be New York State and we'll say that's the wrap called Anna let's add something to our existing regions as well let's go ahead and add something to Midwestern region and let's add to the state Illinois and let's add Bob to this list let's go back and check if this works so now we should have new region here and when I do that I should get C New York and the sales rep should be Anna which was correct well let's go ahead and select Midwestern let's see what we get so Illinois is there so now we should hopefully a Bob we do cool that should be enough thanks for watching please subscribe and I'll see in the next one

Channel: Learn Google Spreadsheets

Views: 187,506

Keywords: Google Sheets, Dependent, Drop, Down, List, Multiple, Entire, Column

Id: s-I8Z4nTDak

Length: 52min 0sec (3120 seconds)

Published: Thu May 02 2019