How To Create MULTIPLE Dependent Drop-Down Lists in Google Sheets

today we're going to create multiple dependent drop-down lists in google sheets so last week i showed you a simple approach to creating a dependent drop-down list this means that the choices you have in the second list is dependent on your first list now that works well if you have one dependent list but what if you want to have dependent drop down lists on multiple rows is there a simple way you can set this up that doesn't require apps script let's find out okay so this is the sheet we're going to be creating our multiple dependent drop-down lists on what are they based on our master data is in a separate tab right here we have three different divisions and then the list of the apps that belong to that division the first drop down is going to have the division so the person can select that and once they do in the second drop down they should only see the list of apps that belong to that division in last week's video we just set up one dependent drop-down list and i showed you different ways that you can set this up depending on how your master data is organized this time i've picked this setup for the master data and i want to create my dependent drop-down list on a separate tab so first off let's do the easy part which is the list for division that's basically the headers that i have for my master data those are these so i'm going to go to the place where i want to have my drop down go to data validation data data validation select from a range my range is sitting right here and it's productivity game or utility click on ok reject input if it's not from my list and click on ok now i have my first drop down right here now here's the thing with google sheets if i copy this drop-down i'm just going to remove the value in it if i copy this and i paste this until here what happens is it shows me the same list even though i didn't fix my selection so remember when we did the range referencing under data data validation here i didn't fix these but it's as if it has invisible dollar signs there now if i go down here and go back to data data validation it's the same range so in case you're used to excel this part behaves differently it automatically fixes the range which can be good but not if you want to make multiple dependent drop-down lists because it's going to make one part of this whole setup a little bit more manual you're going to see what i'm talking about so let's get started with creating the second dropdown i'll just select productivity here so we can see if everything works now because you can't use formulas directly inside the data validation drop down list you have to do a data preparation on a separate site here now in my solution what i'm going to do is create a data preparation for 10 drop downs because i have 10 there so i'm going to set up the 10 here now that's a bit too much so let's remove this now for each of these drop-downs i have to prepare the data set i'm going to use the index function to get this done the first thing i need is the reference which is the range of possible answers i'm just going to go until 20 in this case no need to include a lot more if you don't need them i am going to fix this though because you're going to see why in a second i have to pull this down later so let's go with f4 to fix the entire reference then how many rows do i want to move down well i want to include every single row so every single app that belongs to the division last is the column and we're going to match the column let's go back to our dependent list here and select a3 i'm not going to fix this the range where i want to look this up is here this time i am going to fix so f4 and finally an exact match so we need to go with a zero close and close and press enter and i have the apps that belong to productivity remember productivity was selected switch to game i should see the apps that belong to game now i am planning to pull this down but i don't want it to be vertical i want it to be horizontal so let's go ahead and transpose this so just put it inside the transpose function and that's our list of apps data validation takes horizontal or vertical now if i did the fixing correctly we should get our values here correctly and i think that we do it's just that i have nothing selected so let's test productivity go here and we see the apps that belong to productivity now this any doesn't really look nice so let's put this inside the if and a function if the result is an error basically this n a error we want to see nothing close bracket press enter and let's pull this down okay so that looks good if the data validation in google sheets didn't have these invisible dollar signs all we would have to do is go back to data data validation and select our first range from the master now here let's go all the way up to column z so we're staying on this same row because everything is horizontal and we're just going to include some empty ones in the end that doesn't hurt go with okay and reject input and save the first one all good right if we didn't have these invisible dollar signs i could just copy and paste this down and it would move automatically my data validation to the next lines but unfortunately it doesn't so this is the manual part that you have to copy this down i'm gonna highlight this right mouse click paste special and let's just paste the data validation only now we have to go to each one data data validation and update this to the next row right so this is obviously something you can do if you don't have a lot of rows that need to be corrected right so i'm gonna go with six here and then this one would be the next ones it has to go to seven and save right seven because that's this one right here okay so each one has to have its own possible if you have maybe 10 if you have 20 you know you can be fast getting this done but not if you want it to apply to the entire sheet here for that you will have to use a more complicated solution probably with apps script but in case you don't need a huge range you can use this because each of these is going to be dependent on this one because each of them has their own data preparation table so that's a simple approach you can use to create multiple dependent drop-down lists in google sheets if you like this video as always don't forget to give that thumbs up and if you enjoy the content of this channel and you're not subscribed consider subscribing before you leave thank you for watching

Channel: Leila Gharani

Views: 85,752

Keywords: XelplusVis, Leila Gharani, XelPlus, office productivity, google sheets, sheets, google spreadsheets, dependant drop down lists, dependant lists google sheets, dependent dropdown lists, dependent lists multiple rows, google sheets data validation, drop down list, google sheets tips and tricks

Id: ku17vgq4Q14

Length: 8min 13sec (493 seconds)

Published: Tue Dec 15 2020