Formatting worksheets > Input validation > Settings for input validation

FreeOffice: Input validation is not included in SoftMaker FreeOffice.

As described in the previous section, you can limit the allowed values using the dialog box of the ribbon command Review | Input validation. The following options are available here:

"Settings" tab

▪Allow:

You use these options to define which types of values are allowed:

Option	Explanation
All values	Allow all types of values (thus disable the input validation)
Integer numbers	Allow only integer numbers
Decimal numbers	Allow only decimal numbers
List entries	Allow only values from a predefined list of permitted values.
	Method 1: Enter a comma-separated list of the allowed values in the Source field – for example, dog,cat,mouse or 1,2,3,4,5,6.
	Method 2: Alternatively, you can enter an equal sign followed by a cell range that contains all valid entries. If, for example, cells C1 to C3 contain the values "dog", "cat" and "mouse" and you enter =C1:C3 in Source, only those three values will be allowed.
	The Options button can be used to set additional options (related to Method 2).
Date	Allow only date values
Time	Allow only time values
Text length	Allow only entries (including numbers) with the specified text length
User-defined	Allow only entries which satisfy the specified condition Enter the condition in the Formula field. (For more information on this, see the end of this section.)

▪with values

Here, you can further limit the allowed values. If, for example, you select the option between, you can specify minimum and maximum values between which the values must lie.

▪Minimum, Maximum, etc.

Here, you define the limits of the entries. If you enter 10 as the Minimum and 20 as the Maximum, only values between 10 and 20 will be accepted.

You can also use formulas here. If you enter C1 as the Minimum, the contents of the cell must be greater than or equal to the cell contents of cell C1. If you enter SUM(D2:D8), the contents of the cell must be greater than or equal to the sum of cells D2 to D8.

▪Use dropdown

This option is only available when List entries is selected under Allow. If this option is enabled, an arrow will appear on the right edge of the cell when the user goes to it. If you click on this arrow, the program will display a dropdown list of all permitted values from which the user can select.

▪Ignore empty cells

If this option is enabled, input validation is ignored for empty cells. If the user deletes the contents of a cell, no input validation occurs in the cell until the user enters another value.

"Input message" tab

On this tab, you can enter an info text for filling in the cells (for example, "Please only enter numbers between 10 and 20."). This information will be displayed when the user goes to the cell.

This tab has the following options:

▪Show input message when cell is selected

This option determines whether the input message should be shown or not.

▪Title

Here, you can specify the title of the message.

▪Message

Here, you can enter the text of the message.

"Error message" tab

On this tab, you can specify the error, warning or information message which should appear when the user enters data that does not match the input validation.

This tab has the following options:

▪Show error message after invalid data is entered

This option determines whether the error message should be shown or not.

Note: Normally, you should leave this option enabled, since input validation is of little use if the user is not informed that he is entering invalid data.

▪Type

Here, you can specify the type of message. Simply click on the desired icon.

Important: The type of message also determines whether the user is actually allowed to enter invalid values. PlanMaker strictly rejects invalid entries only when the type Error message (first icon) is selected!

The following types of messages are available:

Error message (first icon): PlanMaker displays an error message. The user is forced to enter a valid value; invalid values will not be accepted.

Warning message (second icon): PlanMaker displays a warning message. The user can decide whether to still proceed with the invalid entry.

Information message (third icon): PlanMaker only displays an information message. The user must only acknowledge this message and the invalid data will be accepted (as long as the user does not click on Cancel).

▪Title

Here, you can specify the title of the message.

If you do not enter anything here, a default text will be used.

▪Message

Here, you can enter the text of the message.

If you do not enter anything here, a default text will be used.

Tip: If you would like to have the specified minimum and maximum values displayed in the message, simply use the wildcards %min and %max. Example: "Error – Only values between %min and %max are allowed here."

Examples

Finally, some examples of how to use this feature:

▪Allow only values within the specified range

On the Settings tab, first select the desired type of values. If only dates are to be allowed, set the option Allow to "Date". If only numbers are to be allowed, set the option to "Decimal numbers" – and so on.

You can then use the option With values to set the allowed limits. If, for example, only values greater than 1 are to be allowed, select the "greater than" option and enter 1 as the Minimum.

▪Allow only values of a certain length

If you would like to prevent the user from entering values with lengths greater than 40 characters, select the following settings:

Allow: "Text length"

with values: "smaller than or equal to"

Maximum: 40

Note: Numbers, periods, commas, signs, etc., are considered to be just as much a character as letters.

▪Using your own formulas for input validation

Advanced users can also select "User-defined" under Allow and then specify any formula for input validation.

To do so, use a formula that returns a logical value (thus, TRUE or FALSE). If the result of the formula is TRUE, PlanMaker will consider the entry to be valid; otherwise it will be considered invalid.

Example: If you enter the formula SUM($A$1:$C$3) > 42, the input will be considered valid only if the sum of the A1:C3 cell range is greater than 42.

Note: Notice that relative cell references can also be used in such formulas in addition to absolute cell references (as in the example above):

Absolute cell references such as $A$1 always refer to cell A1.

Relative cell references such as A1, on the other hand, refer to the cell in the upper left corner of the selected cells. If you apply input validation to only one cell, A1 refers to this cell. If you previously selected a cell range, A1 refers to the cell in the upper left corner, A2 refers to the cell below, etc.