72. Ensure Valid Data Entry
BEFORE YOU BEGIN
64 Conditionally Format Data
70 Protect Spreadsheet Data
Not only can you conditionally format data so that the format changes based on the data (see 64 Conditionally Format Data ), you can also set up data validity rules to help maintain accurate spreadsheets. Once you set up data validity rules, you or those who use your spreadsheets are limited on what they can enter into certain cells .
Data validity A check to determine whether data entered into a cell is valid, defined by a set of criteria that you set up.
Without data validity checks, anybody can enter any value into any cell ( assuming the cell is not protected). Once you set up data validity checks, if someone violates any criterion you set up, such as entering a negative payroll amount, that you deem impossible , Calc flags the entry as an error. If a user types a value that violates any data validity check you've set up, Calc displays an error message you define for that situation.
72. Ensure Valid Data Entry
Select the Range to Validate
Select the cell or range that you want to create a data validity check for. For example, you may want to create a range of dates and disallow any entry into the range that is not a valid date.
To add the data validity check, select Data, Validity to display the Validity dialog box.
| | Set Up Criteria
On the Criteria
page, you set up the criteria to which the range must conform before the user can enter a value. For example, if you require a date that falls after January 1, 2006, you would select Date
from the Allow
field. Keep the Allow blanks
option checked if you want to allow blanks in the range without the blanks violating the criteria.
Select a condition from the Data
field that the range must meet. For example, to allow the entry of dates January 1, 2006 and after, you would select greater than
. Then, you'd type 1/1/2006
in the Minimum
field. The Maximum
field appears if you select either between
or not between
so that you can define the two fields that limit the input range.
The Allow field enables you to set up criteria for dates, times, whole numbers , or decimals. You can also limit the number of characters that can be typed into a text cell.
If you want to clear all the options you've selected in the Validity dialog box and start again, click the Reset button to quickly restore the defaults.
| | Specify Input Help
Click the Input Help
tab to display the Input Help
page in the Validity
dialog box. Here, you set up a floating ToolTip that appears whenever the user selects the cell. The title and message that you type in the Title
and Input help
fields appear when the cell becomes active. The purpose of the Input help
field is to let your users know the kind of data you allow in the cell.
Usually, cells don't require both a title and input help if the criteria is simple. A title such as Must be more than 0 is usually sufficient.
| | Issue an Error Alert
The Error Alert
page describes what happens if and when the user violates the criteria you set up on the Criteria
page. The Action
field can be set to Stop, Warning, Information
, or Macro
, depending on what you want to happen when the violation occurs. When you select Stop
, Calc disallows any entry into the cell until the user enters data that conforms to the criteria. Warning
allows the data but shows a pop-up dialog box with the title and error message you enter in the Title
and Error message
fields of the Error Alert
The Macro option starts a macro program that you or someone else may have written. See 128 About OpenOffice.org Macros for more information about macros.
Test your data validity check by typing data in the cell. When you select the cell, the Input Help message should appear, telling you what data the cell expects. If you enter a value that violates the criteria, Calc responds with a warning or a pop-up dialog box, depending on how you set up the error alert.