Introduction
Text Documents (Writer)
HTML Documents (Writer Web)
Spreadsheets (Calc)
Presentations (Impress)
Drawings (Draw)
Database Functionality (Base)
Formulae (Math)
Charts and Diagrams
Macros and Scripting
Office Installation
Common Help Topics
OneOffice Logo

Validity of Cell Content

For each cell, you can define entries to be valid. Invalid entries to a cell will be rejected.

The validity rule is activated when a new value is entered. If an invalid value has already been inserted into the cell, or if you insert a value in the cell either with drag-and-drop or by copying and pasting, the validity rule will not take effect.

You can choose Tools - Detective at any time and choose the command Mark Invalid Data to display which cells contain invalid values.

Using Cell Content Validity

  1. Select the cells for which you want to define a new validity rule.

  2. Choose Data - Validity.

  3. On the Criteria tab page, enter the conditions for new values entered into cells.

  4. In the Allow field, choose from one of the other options listed to place a condition on a cell entry. "All Values" is selected by default.

  5. If you choose "Whole Numbers", values such as "12.5" are not allowed, even if they satisfy the other conditions. Choosing "Date" allows date information both in the local date format as well as in the form of a serial date. Similarly, the "Time" condition permits time values such as "12:00" or serial time numbers. "Text Length" stipulates that cells are allowed to contain text strings only.

Select "List" to enter a list of valid entries.

  1. Select the next condition under Data. According to what you choose, additional options will be selectable.

After you have determined the conditions for cell validity, you can use the other two tab pages to create input help and error/invalid entry message boxes:

  • On the Input Help tab page, enter the title and the text of the tip, which will then be displayed if the cell is selected.
  • On the Error Alert tab page, select the action to be carried out in the event of an error.

If you select "Stop" as the action, invalid entries are not accepted, and the previous cell content is retained.
Select "Warning" or "Information" to display a dialogue box in which the entry can either be cancelled (the cell value will be retained) or accepted (even if the new value violates the validity rule).
If you select "Macro", then by using the Browse button you can specify a macro to be run in the event of an error.

To display the error message, select Show error message when invalid values are entered.

After changing the action for a cell on the Error Alert tab page and closing the dialogue box with OK, you must first select another cell before the change takes effect.