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
function marks any cells containing invalid data with a circle.
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 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 41 shows the choices for a typical validity test. Note the Allow blank cells
option under the Allow list.
Figure 41: Typical validity test choices
The validity test options vary with the type of data selected from the Allow list. For
example, Figure 42 shows the choices when a cell must contain a cell range.
50 OpenOffice.org 3.3 Calc Guide