| Not only can you conditionally format data so that the format changes based on the data (see 63 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 . Before You Begin 63 Conditionally Format Data 69 Protect Spreadsheet Data 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. 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. | -
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, 2005, 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. NOTE | | 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. | Select a condition from the Data field that the range must meet. For example, to allow the entry of dates January 1, 2005 and after, you would select greater than . Then, you'd type 1/1/2005 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. -
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. TIP | | 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 or Information 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 page. NOTE | | The Macro option starts a macro program that you or someone else may have written. See 127 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. |