Thursday, October 28, 2004

Creating Drop Down Lists Using Validation

You can create a drop down list easily in Excel by using the Validation command.

Validation allows you to apply preset parameters to a cell so that only certain entries can be made in the cell. For example you could limit it to dates between today and 7 days later.

In our example we are going to limit a cell to input from a predefined list using a drop down arrow.

First create a list of several items, eg Apple, Pear, Lemon, Orange.
Put each item in a separate cell, and these cells need to be next to each other either in 1 row or 1 column.

Select the cell in which the drop down list is to appear.
Click Data - Validation from the menu.
In the Settings Tab, choose List from the Allow box.
Click in the source box and click the red button at the end of it to minimise the dialog box then select the range with the list in it.
Click the red button again and then click OK.

You will now be able to use the drop down.

Advanced Options
Some more controls are available in the other tabs in the Validation box. Explore them to see how you can customise the drop down box.
You can copy validation from one cell to another using the Paste Special command.
You can use a range name in the source. Creating a range name for a specific range of cells restricts the drop down to only those cells.
Creating a dynamic range name or a range name that refers to an entire column or row will allow entry into the cell as well as drop down selection from the list. This is useful if the list is only a guide and not the only entries you want to allow.


Posted by: Jethro | 9:30 AM |