Calc Guide

Validating cell contents
When creating spreadsheets for other people to use, you may want to
make sure they enter data that is valid or appropriate for the cell. You
can also use validation in your own work as a guide to entering data
that is either complex or rarely used.
Fill series and selection lists can handle some types of data, but they
are limited to predefined information. To validate new data entered by
a user, select a cell and use Data > Validity to define the type of
contents that can be entered in that cell. For example, a cell might
require a date or a whole number, with no alphabetic characters or
decimal points; or a cell may not be left empty.
Depending on how validation is set up, the tool can also define the
range of values that can be entered and provide help messages that
explain the content rules you have set up for the cell and what users
should do when they enter invalid content. You can also set the cell to
refuse invalid content, accept it with a warning, or—if you are
especially well-organized—start a macro when an error is entered.
Validation is most useful for cells containing functions. If cells are set
to accept invalid content with a warning, rather than refusing it, you
can use Tools > Detective > Mark Invalid Data to find the cells with
invalid data. The Detective marks with a circle any cells containing
invalid data.
Note that a validity rule is considered part of a cell’s format. If you
select Format or Delete All from the Delete Contents window, then it
is removed. (Repeating the Detective’s Mark Invalid Data command
then removes the invalid data circle, because the data is no longer
invalid.) If you want to copy a validity rule with the rest of the cell, use
Edit > Paste Special > Paste Formats or Paste All.
Figure 29 shows the choices for a typical validity test. Note the Allow
blank cells option under the Allow list.
Chapter 2 Entering, Editing, and Formatting Data 49