Creating a pick list dependent on another pick list

- [Voiceover] In this worksheet, we're seeing some entries over in column A, some column headings. Whoever's entering the data here is gonna have a lot easier task if no State entry is typed here. In other words, they emerge from a pick list. And a lot easier if no City names need to be typed here. Now, if someone chooses Arizona, for example, the city names that are going appear here, we want to be like what we see over here in column K. We'd like to have the pick list in column D be dependent upon the entries in column C.

And we could do this, and it involves using an unusual function called Indirect. Let's begin by setting up a pick list for column C. Now, as always when you're setting up pick lists, it's best to have the pick list source data be nearby, so you can see it easily and check it out and so on. Eventually, this data to the right here, it covers not all states but quite a few. We will move this onto a different worksheet probably. We could hide the columns. The ideas is we don't want it to be out there so people could change it. But from the moment it's visible, we want to see it. We're going to click column C.

This is where the State entries will appear. Then, go to the Data tab in the ribbon and choose Data Validation. Settings, drop arrow. The list, the source of the list, and we'll simply highlight these cells from K1 rightward, over to the right, right through Wisconsin. Click OK. Our pick list is set up. We see the arrow there. If we don't want this in C1, not critical. It's not gonna get in the way. But you can simply remove Data Validation from there. But in the other cells here, there's Colorado. Click here.

In other words, the very entries that we just saw over in row one, they're appearing here. And you gotta choose from the list. It's alphabetized. And it's typically done, makes it easier to find entries. But now, what we'd like to be able to do is to somehow say that if we've chosen Colorado here, the only cities we want to see in this pick list are going to come from Colorado. And so, what we first have to do is to name all of these groups over here. And we can zoom back a little bit. What I'm going to be doing here is highlighting all of these cells across and down.

Like this. And there's a feature found on the Formulas tab where we can Create From Selection. Automatically generate names from the selected cells. So, we'll highlight this and choose Create names from values in the top row. Not the left column. From the top row. So, what we're saying in effect is a name called AZ refers to those cells K2 down to K12. Click OK. Now, with that in place, let's set up a pick list for column D.

Data, Data Validation. And what we need here is a list. But the source is going to be equalindirect and will be referring, C1 has no data in it, but we refer to it automatically since it's the act of cell (mumbles). Take out the dollar signs. We always want this to be in the cell to the left of these. So, it's always going to come out of column C. We can leave the dollar sign in front of the C. Right parentheses, OK.

This currently evaluates to an error. That's okay. We want to continue. Yes. 'Cause C1, itself, doesn't work, and that's okay. So, we click here. We've got a drop arrow. Those are Colorado cities. There's Denver. Good. California. Click here. We've got our California cities. On some of these, it might look a little strange at times. Like, is St. Louis the only city? We might have to scroll up here. No, it's not. We got some other cities here. And so on. So, there's one little problem here on some of these, particularly the states that have fewer entries. Let's pick Iowa here.

And then, the drop arrow. We don't see anything until we scroll. Nothing terrible about that, but on the other hand, it would be better if we didn't have that happen. So, here's what we're going to do. Remember, each of these names that we see here applies to cells all the way down into row 12. 'Cause when we highlighted the data initially, we highlighted it like this. So, with this data highlighted, let's select all the blanks. And you can do that by way of Home tab, far right button, Find & Select, Go To Special.

You could also get here by way of the F5 key, and then Special. Choose Blanks. Click OK. The blanks are all selected. Right click, Delete, Shift the cells up. Click OK. Now, it's not exactly obvious, but the name IL, for example, only refers to these cells, not the three below it. And so on. So, now when we go back to the left here as we try this. Here's for Iowa. We see only those two. Pick a different state. Here's Illinois. Drop arrow over here.

We see just those. In other words, we're just seeing the ones that are visible. If there are more than eight, we will see a scroll bar. So, for California here, for example. And we could add cities to that list. We might have to redefine the location, but here we see them. Probably a good idea to keep them alphabetized. Now, eventually, as we fill in other data here and possibly have other columns and so on, we'd want to move this data out of the way. And if we simply select the data and cut and paste it, put it on a different sheet, that would be fine. You could hide the columns possibly. That's another way. But we've shown how to do this.

And potentially, you could even do this at a third level. It'd be a lot more cumbersome. Then, you'd have to have each city and probably have to have a variation, a copy of the list somewhere to set that up. That would take a lot of work. We can see how easily and smoothly it works here. And this could easily be expanded into a 50 state model too, and we could accommodate more cities. Along the way, we'd probably have to redefine what the names mean. If we put in all the cities over 100,000, for example, like California has over 70 of them, so it'd be a huge list. And it wouldn't necessarily need to be put in on that basis either.

So, quite a few options for setting this up, but we've seen how by using this function. Now, the key, again, I'll go back to it in column D. When on the Data tab, we chose Data Validation here. We use this function called Indirect. Indirect is kind of hard to describe, but what it's saying in effect is pick up the data in the cell to its left. And by doing that, we are picking up a range name, CO, for example, or CA, or MO. We're picking up the state code. Now, if you say to yourself what does the state code really mean.

To the left of the Formula bar, if you click out here, there's a drop arrow. There are the names we just created. What does CO mean? It means those cells right there. So, we use a feature called Range. And the indirect function, even though it's referring to cell C2, is really referring to the content of C2. So, it's kind of hard to describe really without an example. But, once again, the key thought here is that in column D, we've got a Data Validation rule using the indirect function that refers to the cell to its left.

And the cell to its left in all these cases, except for row one, is a range name that refers to city names. So, powerful tools brought together here and all at once.