Section 72. Ensure Valid Data Entry

72. Ensure Valid Data Entry


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 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.


The Macro option starts a macro program that you or someone else may have written. See 128 About 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. 2, Firefox, and Thunderbird for Windows All in One
Sams Teach Yourself 2, Firefox and Thunderbird for Windows All in One
ISBN: 0672328089
EAN: 2147483647
Year: 2005
Pages: 232
Authors: Greg Perry © 2008-2017.
If you may any questions please contact us: