Save Signed Dropdown with airSlate SignNow

Save signed dropdown

[Music] in this final data validation course for google sheets i'm going to show you how to change a drop down menu based on the choice of another menu so instead of explaining it let me show you what i want to achieve so here i have a list of items and if i use the drop down it says pizza ice cream salad or hamburger i have pizza here and these options for the pizza are as follows cheese olives onions or pepperoni i can change these as i see fit with no worries okay what about if i change to ice cream i don't want cheese on my ice cream and as you can see we've got a little invalid error warning here we can then choose the drop down menu and choose the toppings for ice cream and as you can note they have all changed so caramel syrup cherry choc sprinkles hundreds and thousands let's go some hundreds and thousands what if we want salad i don't want hundreds and thousands on my salad so again we get a little invalid error warning and we've got our drop down and this time around we have crouton mayonnaise olive oil and vinegar vinegar sounds like a goer lastly we've got hamburgers and this will change to another set of toppings as you can see here cheese slice lettuce meat patty and tomato meat patty sounds like a go too so we'll click on that so there's one way to get this done and i highlighted it in our second tutorial on data validation way back when we did lists from ranges where we use relative items this can be a little bit messy in the long run and not a great way of storing data on your google sheets so let's head back to our change on choice tab and open up these items here let's scroll over so imagine these are in a separate sheet that i usually keep called a notes sheet so i generally create it and then i hide it you can hide a sheets tab by right clicking and going to hide sheet you can even protect the sheet as well as you can see here okay so first i have a list of items these items are my pizza list ice cream list salad list and hamburger list this is a much easier way or much more accurate way of displaying your data instead of having a random list of unknown quantity of items along the side here and it's much easier when you're trying to convert this list into a database or something like that down the track so for all our pizza items we have our toppings and then our ice cream we have those toppings salad and hamburger for all those toppings as well our next step is to get a unique list of all our items we don't want repeats of these pizzas ice creams and salads so first off we need a unique list of all our items so we do this with the unique function so here we've got our unique function and it selects from i3 through to i19 it could be i3 through to i 100 or whatever it doesn't really matter and what it does is basically goes through the whole list it could be a scrambled list of items and finds just the unique item of each one then to help our users out we want to sort it and we sort it alphabetically and this is our end result if we have a look at our data validation for that first item we can click on a2 here right click go down to data validation and you can see i've selected the range from f3 through to f8 i've got a little bit of an overlap just in case we add some more in okay so we've show drop down list we'll reject the input and we'll show validation help hit save cool and this is our list of items here okay so before we move on to our options we need to create an option list that appears just for those particular items we can do that by filtering them out so before we start have a look at what happens in this column g when i change the item so if i change it to ice cream you can see that the toppings have changed if i change it to pizza same again salad changed again so what's going on here i've used a filter and a filter reduces down your items to a selected amount based on the values that you put in or the arguments that you put in the first argument in the filter is the range that you want displayed and for us we want our toppings displayed so i've selected here j3 the j100 which is our list of toppings now you can have as many arguments as you want but we only need one argument here and basically what that says is if any item in column i is equal to a2 then we want to display those toppings so for example right now we have salad in a2 so a2 equals salad so it's going to say all right i found salad here and i want to display the j here i want to display all the items of those salads so it's going to be the croutons vinegar olive oil and mayonnaise and if you have a look over here that's what they're being displayed to create this options list all we need now is to use our data validation and select these options so what we do is right click go down to data validation and we choose list from a range and we select the range g2 and here i've got a bit crazy and selected g hundred it doesn't matter list from range will only select those cells that have values in them and ignore anything else we showed our drop down list this time we'll reject the input and show validation help and click save so now when we've got our salad it'll come up with our mayonnaise ice cream it'll come up for the warning and we'll say caramel syrup and it'll change as we go i hope you'll find that useful in your own projects that's it for the data validation course for now if you have any questions please put them in the comments below if you like the course please hit like and if you want to see more courses why not hit that subscribe button i'll catch you in another tutorial