Tuesday, May 22, 2007

Data Validation (aka, Dropdown Box)

If you want to use Excel as a Data Collection Tool, Data Validation is an absolute must. Data Validation allows the user to pick a value from a drop down box. That's not all it does, though. You can also add a data validation that will only allow values that are within a predefined range. You can also add data validation that checks the text length and makes sure it's within a predefined range.

When you set up data validation so that the user can pick a value from a list, the user will see a drop down box. In all other instances, the user will NOT see a drop down list.

Choose Data-->Validation and you'll see this dialog box:

If you choose whole number, decimal, date, time or text length, you get these options:

Either enter values in the boxes, or choose a cell that contains the value you'll be using to validate your data. The nice thing about choosing a cell is that you can change what is being validated without actually going into this box.

The other two tabs are pretty straightforward. On the input message tab, you have three entry options. The first is a check box that allows you to Show Message when cell is collected. The second is Title, and the last is the message. Here's a neat trick. Let's say you choose Any Value on the settings tab (so, your data validation isn't validating anything. It's just allowing ANY VALUE). Then you check the checkbox on the Input Message Tab. Then you enter a title and message. When your user lands on the cell with the data validation, they get a pop-up message. You get the message without restricting what data goes into the cell. Or, restrict the data, and still get the pop-up message. Either way is fine. But, if you'd been looking for a way, besides comments, to alert your user to something when they land in a cell, use Data Validation for its messaging. It's a good thing.

The last tab is Error Alert. You only use this if you are actually setting up a data validation that restricts data (You don't need it if you choose ANY VALUE on the settings tab). When your user enters invalid data, they get your error message that you created telling them what they did wrong. There are 4 inputs on this tab:
1. A check box allowing you turn off or on the error message.
2. A style: choose between STOP, WARNING, or INFORMATION.
3. Title
4. Error Message.
If you need to inform your user using Excel, be sure to include an error message.

I want to focus on lists. I've never used custom, and all the others are pretty straightforward. So, lists.

On the settings tab of the Data Validation Dialog box, when you choose Lists, you get 4 other inputs.
1. Ignore Blank. Check this and the user can make it through the cell without choosing or entering a value.
2. In-cell drop down. Check this, and your user can choose from a list of values.
3. Source. Here, you have options. You can enter the list right there in the box, separating the values with commas. Or, click on the zoomy box, and then highlight the list you want your user to choose from. If you do this, you are limited to a list of data on the worksheet that contains your data validation. If you want to move your lists of data to another tab, you can do that, too, but it's not quite as straightforward. But, it's easy once you've done it a couple of times.

Go to another tab and do whatever you have to do get your list together (type it, copy and paste, whatever suits you.) Now, highlight that list. Put your cursor in the name box, and type the name of the range. Name it whatever you please. But, wait! Where is the elusive name box? you ask.

There it is. In the top left corner. I circled it and called it NAME BOX. If you don't type over it, it normally contains your current cell address.

Once you have your named range, in the data validation dialog box on the settings tab, type =NAME BOX (except substitute the name that you have chosen. If you need to change the list in the future, choose Insert-->Name-->Define. This will bring up a dialog box where you can change the cells that are included in your named range. Your named range can only be one column of data if you plan on using it for a drop down box for data validation.

Here's what it looks like:

If you or your user tries to enter a value that is not on your list, they will get an error message. They either get the error message that you defined or a generic message.

Drop Down Boxes and Data Validation.

Fun for all.

1 comment:

Anonymous said...

Is there a way to use one named list as the display and another list as the values inserted into the cell. For example

ID Fruit
1 apple
2 pear

I want the dropdown to show apple but insert 1 into the cell.

brock.beatty@gmail.com