OpenOffice.org 2, Firefox, and Thunderbird for Windows All in One [Electronic resources]

Greg Perry, M. T. Cozzola, Jennifer Fulton

نسخه متنی -صفحه : 231/ 96

72. Ensure Valid Data Entry

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.

KEY TERM

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

[View full size image]

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.

128 About OpenOffice.org Macros for more information about macros.

Test Validity

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.