|
Suppose you have a cell in a worksheet that must contain one of
a finite number of entries (days of the week, for instance).
There is a way that you can not only restrict the entries allowed
in the cell, but also assist the user by offering a drop-down
list automatically if they select that cell.
|
|
|
First, with the cell pointer on the cell in question, use the Excel menu
sequence 'Data, Validation...'. A dialog box will appear as shown here.
Go to the "Settings" tab. Set the "Allow:" field to "list".
|
|
|
You can type a list of values straight into the "Source:" field as shown here
(Monday,Tuesday,Wednesday,...) or you can set the "Source:" field to
the range of cells where your list is held (for example, =$C3:$C7).
This source range must be in a single row or column.
Note that a selector
box appears which allows you to point to the required source range, but
this only works if the list is stored on the same sheet. To use a list
located on another worksheet, define a name for the list-source range,
and in the "Source:" box put an equals sign followed by the name
(example: =ListSourceRangeName).
|
|
|
Once you have done this, a drop-down button as shown will appear
whenever you select the cell in question.
You can even have a pop-up prompt appear alongside the drop-down
arrow. Its title and text are entered via the "Input message" tab
on the Data Validation dialog.
|
|
|
Clicking the button activates the drop-down list and you can complete
the cell by pointing to the item you want.
The same validation rule can be applied to other cells by using the
Copy, Paste Special, Validation command.
|
|