Setting up Data Validation Lists in Excel

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Setting up Data Validation Lists in Excel

This is the first in a series of articles about the Excel techniques behind my March Madness worksheets. Today, a quick look at the Entry form and how to set up dropdowns in Excel.

In the bracket worksheet, there are dropdowns in all of the cells where you need to select a winner.

I made these dropdowns be a different color by using the paint bucket icon in the formatting toolbar. If you click the little dropdown next to the paint bucket, you can access more colors.

To add the dropdown, select the cell and use Data - Validation. Change the Allow box to be a List. You can then specify where your list is stored.

You will see that my list is stored in a place called =List01. This a named range. Most of the time, you could just set up a list off in the far-right unused columns of the spreadsheet. You could say your list is in =AA1:AA2. However, I always have a fear that someone would accidentally delete row 2, wiping out my list. So, I prefer to keep the lists on a hidden Sheet2.

If you use Format - Sheet - Unhide, you can Sheet2. There is a fair amount of data stored back here.

To find a particular list, use the dropdown to the left of the formula bar. Here is List03.

Here is the somewhat interesting thing. How do you make the second round dropdowns work? They seem to be smart enough to know that you selected Duke and UNC Wilmington.

If you look at the Data Validation for this dropdown, you will see that it is defined as List33.

Find List33 in the Name dropdown. You will see that it is cells AG2 & AG3 on Sheet2. When you look at these cells, they are formulas that point back to cell B4 and B8 on Sheet1. This is kind of cool - you set up validation to point to a range on the worksheet and that range contains formulas. As long as you don't try to use the dropdown in Round 2 before selecting the winners in Round 1, everything works fine.

In the next article, we'll take a look at more of Sheet2. For the BEST TV show on technology, check out Call for Help.

This tip was originally published on March 15, 2006.

The permanent URL for this page is http://www.mrexcel.com/tip123.shtml.

If you are looking for show notes from another episode, visit my complete list of TechTV appearances.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.