New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Creating multitiered lists

From: Excel 2010: Data Validation in Depth

Video: 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.

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.

Show transcript

This video is part of

Image for Excel 2010: Data Validation in Depth
Excel 2010: Data Validation in Depth

17 video lessons · 9047 viewers

Dennis Taylor
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.