Creating dependent dropdown
lists in Google sheets can seem quite tricky at first. But they're actually quite easy once you figure out this
one trick, let me show you. (upbeat music) Okay. So I'm going to
create two dropdowns. The second dropdown will be dependent on the first dropdown. The information I have is about division and the apps that belong to each division. So this means that the first dropdown has name of the division, then once the person selects the division in the second dropdown, they can select the app, but they can only see the apps
that belong to that division. Now, this here is my master data. Now I've organized it
in two different ways because you might have your master data either in a tabular format
or in a report format. It doesn't matter how they are formatted. It's just important that you
figure out the right formula to get this done. So I'm going to do both versions here, pick the one that fits your data best. In the tabular format, our divisions are in a single column, and then in front of it, we have the app that
belongs to the division. In the report format, we have the divisions as the headers, and then we have the apps
that belong to that division underneath it. Same data, organized differently. Okay, so now I'll start off
with the tabular format first. I need the dropdown for division. Now the great thing about Google sheets, is that you don't need to
get the unique division in a separate list, the data validation
automatically gives you the unique records. So we basically don't have to do anything. We just have to go to
data, data validation. I want list from a range,
and my list is right here. Now I can go to the bottom of the range or I can leave it open-ended, so that I don't have to worry
about where the data ends and go with okay. I also want to reject input if the selection is not from
the list and click on save. Now, just one thing I'm
going to mention right now, is that just for simplicity, I've put these on the same sheet where I'm doing the dropdown. But that's not going to be
the case in the real world. You're going to have these in
a separate master data tab, and you're going to have your
dropdowns in your final report. So you're going to keep these separate but I didn't want to keep
moving back and forth and get you dizzy with that. So I put them for practical
purposes on the same sheet. Now the steps you do when
they're in a separate sheet is identical to what I'm showing you. Here, you just have to
click back and forth to make your selections. Okay, so notice that I
got the first dropdown and the values here are unique. Now comes the difficult part. How do I create a second dropdown list, that only includes the apps that belong to the division here? Now to think with the
Google sheet data validation is that you can't put a formula directly inside the formula box. You need to do it under grid somewhere and then make a direct reference to that range inside the data validation. Your formula doesn't
go inside the box here, your formula goes on the grid. Basically what you end up doing, is creating something called
a data preparation list. So what formula can they use here to get the apps that belong
to the productivity division? A great function for that,
is the filter function. I have a detailed video on this in case you want to find out more I've added the link to the
description of this video. The range is the range we want returned. We want the apps. Now I'm also going to
leave this open-ended. Next the condition. Well, we want to include every single app where division equals this division. So that condition is to check this range. We have to be consistent
with our height of the range. I'm going to remove that reference. If that equals this one right here. And that's it. Close bracket, press Enter, and I have the apps that
belong to productivity. Now, if I change this to utility, I get the apps for utility. Now, all I have to do is go to where I want to
have my data validation, go to data, data validation,
and do the same thing. This time, reference my data prep. And I can also leave this
open-ended, click on okay, reject input if it's not
from my list and save. And now check this out. I get the apps that belong to utility. Now, if I switched this to game, I get the apps that belong to game. Okay? So that's if your data is structured in a tabular format like this. Now what if we have
this type of structure? Well, let's just group these together. Let's start off with a
dropdown for division. Let's go back to data validation. The range this time is horizontal. That's no problem for
data validation either, reject input and save. We have the first dropdown,
now the second one. We know we need to do a data prep. Let's do it on the side here. What formula can I use to get
the members of each division? Well, again, it depends on
what type of formulas you know. My favorite function or one
of my favorite functions, is the index function. The reference is the range
that I want returned. That range is the range
of possible answers. Possible answers are here. Now, again, it's up to you how much do you want to reference. This time let's just go to 30. We don't need to go all the way down. 30 should be enough. Okay, so that's my range
of possible answers. Next is how many rows
do we want to go down. Well, we want to include every single app. So I'm going to skip this because I want to
include every single row. Last one is the column. Here, I can match it. I'm going to use the match function, what am I looking for? This one right here. Why am I looking it up? Right here, and I want an exact match, so I have to put a zero here. Now I also have a video
on index and match. If you're curious to find out more, link to it will be available. So close two brackets, one for match one for index. Press Enter, and I get my results spilled. So I have utility. That's why I see this column. If I switched this to game, I see the apps that belong to game. And productivity, the
ones for productivity. Now that I have the hard part done, I go to the easy part,
data, data validation. My list is right here. Now again, we can go as
much as we did before. I think it was 30 click on okay. And save. And we have the members of
the productivity division. Now there is one thing
you need to keep in mind that if you've already
made your selection, and then you go back and
you change the division, this one becomes invalid. It still shows it, but it's invalid. So you have to click. And the moment you click on the dropdown you will see the right members. Problem is this doesn't
automatically reset when you change it. To make it reset, you
have to use app script. This is something I cover inside the Google sheets masterclass. Now you might be wondering, what if I wanted this
repeated on multiple rows? So what if I wanted to have 10 rows that has a dependent dropdown list? That's something we're going
to cover in next week's video. So that's how you can create
dependent dropdown lists in Google sheets. You just have to get creative
with what's available to you. If you liked this video don't
forget to give it a thumbs up. And if you like what
you see in this channel, you want to improve your
skills, consider subscribing. Thank you for watching and
I'll see you in the next video. (upbeat music)
