How can we create a
dependent drop-down list on every row in Excel? So I've made a few videos about how you can create dependent
drop-down lists in Excel. I even have an entire
playlist dedicated to it. Here's a common question I get. How can I create multiple dependent data validation lists on the same sheet. There are different approaches to this, and it can get quite complicated. I'm going to show you
a version that requires one single formula that gets the job done. (upbeat music) We're going to create our
dependent drop-down list in these rows right here. The first choice is to select a division, and the second choice is to select an app. Now the app choice that
we're going to get here is based on the division
that's selected right here. Now you can see our data set here. We have the divisions listed on top and these are the apps that belong to each division. Now for the purpose of the video, I've put this data set
right beside my selection just so that it's easier for
us to create the formula. You're probably going to have this sitting on a separate tab or sitting, well on this side, hidden from view. First off, let's start with the division. That's the easy one, right? That's just a drop down
containing these three, so let's go ahead and do that. Go to the data tab, click on data validation right here. Under list, we're going to
go with this as our source, and click on okay. So notice everything is fully fixed, which is perfect because
I'm planning to copy this and paste it to here. Okay so now when I click on this, I can select between
these three divisions. Now, here comes the difficult part. We need to create a
dependent drop-down list that's based on this. So if the user selects utility, we want to give the user
a choice of these apps. I'm going to do that
using the offset formula. Now just to be sure that I get the formula written correctly, I'm going to write it right here. Once I have a good feeling that it works, then I'm going to copy it
into the data validation box. So the formula that I'm going to use is the offset function. Choices for offset are right here. Now I also have a separate
video covering offset in more detail, and I've added it to
description of the video and also to the cards. So make sure you check it out if you need more information about offset. First argument is the reference. Reference is your starting point, because what the offset function does is it gives you back a range
that's a few steps away from your reference point. Now those few steps are things that we can define right here. So it makes sense that our reference point is close to our data set. So I'm going to go with
the header right here. Since I'm planning to copy
and paste this formula in data validation and
apply to many cells, I'm just going to make
sure I do the fixing for it correctly. So this cell has to be fully fixed. Next one is the rows. So how many rows do I want to move down? Well, the apps actually start one below the reference point, so I want to move one down. Next is the column. How many columns do I want to move across? Well, that depends on what
the division is, right? If the division is productivity, I don't want to move anything. I want to stay here. If it's utility, I want to move two. To get that number, I'm going
to use the match formula. First argument is look up value. We're looking for this
division right here. Now I'm just going to fix the A column. The next argument is our look up array. I'm looking for it in here. I'm going to fully fix
this by using the F4 key. Then I'm looking for an exact match, so a zero here. If I just highlight this and press F9, if it's utility, I see a three, right? Because it's the third position. So here's the thing, the match function starts
to count from the first one. So the first one is a one, a two, a three. Offset is a bit different. It starts to count from zero, one, two. So a three for offset would
be outside the range here, which means that we have to
make a correction to this and take away one from the
results of the match function, because this will give us a two, which tells offset move one, two. I'm just going to press
control Z to go back. Okay, so until now, we know the right column. Now here's the tricky part, the height. So, we went one row down, and we know which column we're on. So we're actually at this moment sitting in cell H6. Now just to double check that, let's give a height and
a width of one, one, which means give me one cell back. The height of 12 or 15 would
be give me 15 cells back. I just want one cell just to double check that I'm really sitting
in Commuta right now. So I'm going to close bracket, press enter and that's Commuta. But if I change this to Game Div, I see Fightrr. Okay, so until now, everything is working fine. But I obviously don't want
to just have one cell back, I want to have all of these back. Here's the other challenge that we have. The first list has 15. Then we have the last, I think this is 12, and then we have 13. If I give it 15, I'm going to have empty cells
on the bottom of my drop-down. Now if you don't mind that, you can go ahead and fix
this to a specific number. If you want it to be restricted, it's a bit tricky. So I'm going to show you both. Now just to demonstrate, let's change this to 15. If I go into formula bar and I press F9, I can see all the apps listed here. The last two are showing as
zero because they're empty. I'm going to press control Z to go back. This means that my formula works so far. So what I'm going to do is copy this. I'm just going to press escape to leave. And now I'm going to
go to data validation, go to lists, and paste
my formula in there, and then click on okay. Now when I click on this drop-down, I have Commuta, all the way to the end. But I see these empty ones as well. So let's just make sure
this works until now. I change this to Game. I go here and I see game apps, but I have these empty ones
on the bottom right there. So now let's deal with those empty ones. Let's restrict the list
that it's the correct size for each division. What this means for us is, we need to get this part of the formula that we fixed to 15, we need to get that part dynamic. So let's find a formula that
gives us a correct count of apps based on the
division selection here. Once we figure that out, we can replace that 15 with our formula. So I'm just going to test it out here. There is the formula, the count A formula, that's going to count
the text values we have sitting in these cells. So what is this going to give me? 15. If I point this to this part here, what am I going to get? 12, right? Because I have 12 text
values sitting in this range. Now I want to make this
dynamic so that this range is based on this selection right here. Which formula can I use to do that? The same formula, right? I can use the same offset
formula to get this effect. So let's try it. Lets do offset. Let's start off where we started before. I'm going to fix that reference. Then is the number of
rows we want to move down. We start one below, is the number of columns we want to move. That was the match function. We're going to look for the division here. We're going to fix the column. Then for the lookup array
that's sitting right here, let's fix it. We want an exact match. Now we're going to take away one so that we give the correct position to the offset formula. And now for height, I'm
going to fix that to 15. Basically you can fix
it to the maximum number of values, of text values, you're going to have in your drop-down. So I could even fix it
to 20 if I wanted to, if I expected to have a
division having 20 values here. So just pick a number
that is safe for you. So I'm going to go with 20, and the width by default is one because we just want to have one column. So I'm going to close the bracket, and close again for the count A formula. When I press enter now, what will I see in the cell? 12, right. Because I'm pointing to the Game Division. Now let's just double
check that this is dynamic. If I switch to Productivity, what should I see? 15, that works. And Utility, I see 13. Right? So now my count formula is dynamic, which is perfect, so I'm going to copy this and go back to my data validation and replace this 15 very carefully with my
new count A formula here. And then click on okay. Now let's make sure that I don't get those empty cells on the bottom, and I don't. It stops on the last app here. This looks good so far. Let's just copy and
paste the data validation to this range here. So I'm just going to copy this, right mouse click, go to paste special, select validation and okay. So now I've just pasted the validation. So now, let's just check. I'm going to go select Productivity and I see the apps that
belong to Productivity all the way to the bottom. So let's just make a selection here. Now let's go and select Game, and now we should see the
apps that belong to Game, and let's just check. We stop at Arcade. Okay, so we used an offset formula and actually we used two offset formulas. With an offset in an offset, we were able to create
multiple dependent drop-down lists in Excel. Okay, I said one single formula, but I didn't say it
was going to be simple. I just prefer this approach
to using a combination of other approaches, like using indirect or substitute. I just find it easier to use
a single formula instead. I hope you enjoyed this video. If you did, give it a thumbs up. And if you aren't
subscribed to this channel, consider subscribing, and hit that bell so that you can get notifications when I put out new videos. And I'm going to see
you in the next video. (upbeat music)
