Join Dennis Taylor for an in-depth discussion in this video Creating multitiered lists, part of Excel 2007: Data Validation in Depth.
In this worksheet, there is a data validation rule in column A that allows us to pick state names. We see them here. Recognize the state names also appear in column D as the source of the list. Now, it would be really powerful if in column B we could pick a city out of this list of cities here. But of course if we've chosen California, we would like the city names to be only these next to California. In the case of Indiana right here, we would just want to see these names pop up.
So, using different features of Excel, including data validation, we want to set up a pick list in column B that's going to vary and it's going to be based on the entries in column A. This feature otherwise doesn't have an official name in Excel. Maybe we call it multi-tiered list, multi-tiered pick list. And setting it up isn't really that difficult. It's going to take just a little bit of time. In column B, we want to see the state names. Now, let's click column B, set up the data validation rule, and what we're going to be using here is a function that perhaps you have not used.
I don't use it very much in other circumstances, but it certainly works here. And the Allow option here is List, Source equals, and the function is called INDIRECT. It's a hard function to explain except by way of example. Now for every entry in column B, we want to be basing on the entry in column A. At the moment, the active cell is in B1, so we want to refer to the column A entry, so we put in A1.
That's how we start the set up, and this simply means that our entry B1 and A1 which contain the word City and State, we don't care. We do want to continue here. Now, we now need to assign these city names here--the name AZ, and these city names CA, and so all the way across. So let's select this entire list, and rather than doing this many times, let's use a feature available on the Formulas tab that allows us to create names from Selection, automatically generate names from selected cells.
This is a great little feature. Create range names from values in the Left column. In other words, the range name AZ is going to refer to a list of names that includes Chandler, Gilbert, Glendale, Mesa, et cetera, all the way across, and in California: Anaheim, Bakersfield, and all the way across, and so on. Click OK. Now, earlier our reference used indirect, and so the indirect reference that refers into column A in this example is referring to CA.
So what do we see in our list now? Those California cities. Maybe it's Los Angeles here. This is an Indiana entry. Which city are we looking for in Indiana for our list here, and we might see it this way. We have to scroll up and find it. Perhaps it's Indianapolis, and so on. Then we'll try another state here. Let's pick Colorado, and the entries for Colorado are these. Let's pick Fort Collins. There we go, and so on. We see how it works. Now there is a slight flaw in it, which can be remedied.
It's not a big deal here, really. I am going to pick Nebraska. There are only two cities listed there. And we don't see them at first, so we have to scroll up and find them. So that's a slight annoyance. And the reason is that when we define these names, we use this entire region, so all those empty cells to the right are part of the definition. Let's select these and delete those spaces through a not-so-well-known feature, but easy to get to: on the Home tab, the rightmost button, Find & Select > Go To Special.
We're going to choose Blanks. Click OK. Just the blank cells in this range are selected. Next step, right-click and delete them, Shift the cells leftward, click OK. And now that little annoyance we saw from before is no longer going to be there. We only see two entries here. That was Omaha. And we can proceed with this list as we choose not having those blanks there. So it's an ideal feature, and in theory, we could even have a third level if we had stores within each of these cities.
That's going to take a lot more work as well, but you get the general idea. A powerful feature to be sure: multi- tiered data validation--one pick list based on another pick list.
- Testing for whole numbers and decimals
- Using the input message box
- Sequencing and placing lists
- Creating multi-tiered lists
- Setting date and time limitations
- Limiting text length
- Locating data validation rules