Creating multitiered lists
Video: Creating multitiered listsIn column A in this worksheet, data validation criteria have been set up so we click the arrow, choose the state we want. Now what if the city choices that we need here will vary based on the state? What if we can have a pick list here that only showed us the California cities, a pick list here that only showed us the Colorado cities? In other words, a pick list that depends upon data that's in the adjacent column to the left. Now this features are a little tricky to set up, but it's really powerful.
Viewers: in countries Watching now:
In Excel 2010: Data Validation in Depth, author Dennis Taylor shows how to use the data validation tools in Excel to control how users can input data into workbooks and ensure data is entered consistently and accurately. The course covers creating dropdown lists, preventing duplicate entries, and controlling the format of numeric data, dates and times, and text entered into worksheets. Exercise files are included with the course.
- 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
Creating multitiered lists
In column A in this worksheet, data validation criteria have been set up so we click the arrow, choose the state we want. Now what if the city choices that we need here will vary based on the state? What if we can have a pick list here that only showed us the California cities, a pick list here that only showed us the Colorado cities? In other words, a pick list that depends upon data that's in the adjacent column to the left. Now this features are a little tricky to set up, but it's really powerful.
I think you can see that it could be applied to other kinds of data as well. We want a pick list in column B based on what happens in column A. Column A is already set up. Once again, if you are curious about how those rules are established, just click on one of these cells, go to the Data tab, choose Data Validation, and you'll see a rule that shows that this list is coming out of the cells D1 to D12 on this worksheet. You may eventually want to have this list placed on a different worksheet, but for now it's right here, just fine.
Now in column B, we want to be able to say that whatever is in column A is going to guide us as to what we will be choosing, and the not-so-obvious choice is going to be a function called Indirect. Now, we're selecting column B, and so for the moment the active cell is B1. As we go to data Validation, the Validation criteria under Allow will be List, and here is the unusual step: =indirect, and we're going to be referring to cell A1.
Just type it in. There it is. Now, because B1 is the active cell, it means--even though we're not saying this literally--that for every cell in column B, we want to be looking at the data to its immediate left. Now A1 and B1, we don't really care about; nevertheless A1 is referred to in the formula. As we click OK here, this message, a bit disconcerting, simply tells us that the indirect reference to A1 doesn't really work, and we don't care about that.
Do we want to continue? Yes, we do. Now, this doesn't work yet. Click the drop arrow. Nothing happens. We want CA, or California, to refer to these names. We want IN for Indiana to refer to these names, and so on. So let's select all of these cells here, and then on the Formulas tab, Create from Selection. What we want to do is to create a range name called AZ and have it refer to those cells to the right, and CA and have it refer to those cells to the right, and on and on and on.
We want to create names from values in the left column, not the top row. Click OK. So CA, the range name CA, refers to all these cells, and so when we click here and use the drop arrow, we see the California cities. Maybe it's Sacramento here that we are choosing. For Indiana, we're seeing South Bend. We might have to scroll here. We want to use Fort Wayne. For Colorado, we see some cities. We want to use Denver, and so on. And as we pick new states here and there, different states, or whatever states, we will see the choices we need.
There is one aspect to this that's a little disconcerting, and that is that we do have to do some scrolling sometimes, and that's because, for example, like in Iowa, the name IA refers to all of these cells, including the empty ones. So there is a fix for this as well. Let's highlight all of these cells here, and get rid of the blanks, by doing what? On the Home tab, the extreme right button, Find & Select > Go To Special. Choose Blanks, OK.
Just the blank cells are selected. Right-click on any of the blank cells, Delete, Shift the cells leftward. Now, that's the least critical part of what we've done here. But from now on, after we've chosen the state--actually choose states that doesn't have too many entries in it for the best reference-- click Kansas here. The choice drop arrow, we don't see those trailing spaces. There we go. So, a power tool to be sure, that any feature that takes away our ability to--or our error-prone ability to--make typing mistakes is great, and so using a two-tiered-- for want of a better term here--data validation setup that involves pick lists is very, very efficient.
There are currently no FAQs about Excel 2010: Data Validation in Depth.