Page 88 - Excel Timesaving Techniques for Dummies

16_574272 ch14.qxd 10/1/04 10:39 PM Page 73
Only the Valid Need Apply
cell of the worksheet to which you then refer. 73
(See Technique 15 for an example of data valida-
tion using the Custom setting.)

By default, all data validation settings regard
blank cells as valid. If you want to prevent
users from leaving a cell blank, while at the
same time making their entries conform to
your other data validation criteria, clear the
Ignore Blank check box on the Settings tab.

Data entries from a list

Enabling users to do data entry by selecting the data
from an in-cell drop-down list is one of the most pop-
ular uses for the data validation feature. Not only
does this type of data validation prevent users from
• Figure 14-1: The Settings tab of the Data Validation selecting an invalid entry, but it also enables them to
dialog box is where you set the validation make a correct entry without having to do any typing.
criteria.
Figures 14-2 and 14-3 illustrate how you would use
List restricts data entry to one of the items on a the data validation List setting to create an in-
list (whose items must already have been entered cell drop-down list. In Figure 14-2, I started a new
in a range of cells in the worksheet). When using worksheet by entering a list of cities in cell range
this Allow option, Excel enables you to create J2:J7 (with the help of the AutoFill feature — see
an in-cell drop-down list (with its very own tiny Technique 13). This list represents the only valid
drop-down button) from which you or your users entries for the City column of my new Account Rep
can select the entry.
Assignments table.
Date restricts data entry to a date that is equal
to, before, after, or within a range of dates that
you then specify.
Time restricts data entry to a time that is equal
to, before, after, or within a range of times that
you then specify.
Text Length restricts data entry to a number of
characters that is equal to, greater than, less
than, or within a range of numbers that you then
specify.
Custom restricts data entry to the parameters
specified by a Logical formula (one that evalu-
ates to TRUE for allowable entries or FALSE for
invalid ones) that’s already been entered into a

• Figure 14-2: Setting up a list of valid city entries for the
new table of account rep assignments.