Formatting worksheets > Input validation > Settings for input validation

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

"Preferences" tab

▪Allow:

With these options you can specify which types of values are allowed:

Option	Explanation
All values	Allow all types of values (effectively deactivate 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 permissible values.
	When you use this option, 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.
	Alternatively, you can enter an equal sign followed by a range of cells which contains a list of permissible values. For example, if the cells C1 through C3 hold the values "dog", "cat" and "mouse" and you enter =C1:C3 in Source, only those three values will be allowed.
	Additional settings are displayed when the Options button is clicked.
Date	Allow only date values
Time	Allow only time values
Text length	Allow only entries whose length falls within the bounds specified here.
Custom	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 narrow the bounds for allowable values. For instance, if you choose the Between option, you can specify minimum and maximum values, which the input value must lie between.

▪Minimum, Maximum etc.

Here you can specify the bounds of the entries. If you enter 10 as the Minimum and 20 as the Maximum, only values within 10 and 20 are accepted.

You may also use calculations 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. Should you enter SUM(D2:D8), the contents of the cell must be greater than or equal to the sum of cells D2 through D8.

▪Use dropdown

Only available when List entries is selected under Allow. When this option is selected, an arrow appears on the right edge of the cell when the user goes to it. When clicked, a dropdown list appears, which contains all available values for that cell, and from which the user can choose.

▪Ignore empty cells

If this option is enabled, input validation is ignored for empty cells. If the user deletes the contents of a cell, the cell does not run any input validation until the user enters another value.

"Input message" tab

On this tab, you can enter informational text that pertains to the cell being filled out – for example, "Please enter only numbers between 10 and 20." This information is displayed when the user goes to the cell.

This tab has the following options:

▪Show input message when cell is selected

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 informational message, which appears when the user input does not pass validation.

This tab has the following options:

▪Show error message after invalid data is entered

Determines whether the message should be shown or not.

Note: Normally, you should leave this option checked, since input validation is of little use if the user isn't immediately notified of an invalid entry.

▪Type

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

Important: The type of the error window also determines whether the user is actually allowed to enter invalid values. PlanMaker strictly rejects invalid data 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 they proceed with the invalid entry anyway.

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

▪Title

Here you can specify the title of the message.

If you enter nothing here, a standard text is used as the title.

▪Message

Here you can enter the text of the message.

If you enter nothing here, a standard text is used as the title.

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

Examples

Finally, some examples of use:

▪Allow only values within the specified range

On the Settings tab choose first the desired type of value. 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", etc.

After that, you can set the bounds using the With values option. For instance, if only values greater than one should be allowed, choose the "greater than" option and enter 1 as the Minimum.

▪Allow only values with a certain length

If you would like to prevent the user from entering values with lengths greater than 40, choose the following options:

Allow: "Text length"

With values: "smaller or equal"

Maximum: 40.

Note: Characters such as numerals, periods, commas, symbols, etc. count here just as much as characters such as letters.

▪Using your own formula for input validation

Advanced users can also choose "User defined" under Allow, and then give a formula for use in the input validation.

To do this, use a formula that returns a logical value (i.e., TRUE or FALSE). If the result of the formula is TRUE, the input is treated as valid; if not, it is treated as invalid.

An Example: If you use the formula SUM($A$1:$C$3) > 42, the inputted data is only treated as valid if the sum of A1:C3 is greater than 42.

Note: Notice that not only absolute cell references, as above, can be used, but also relative cell references:

Absolute cell references, like $A$1, always refer to the cell A1.

Relative cell references, like A1, on the other hand, refer to the cell in the upper left corner of the selected cells. If you use input validation on only one cell, A1 refers to this cell. If, beforehand, you had marked a range of cells, A1 refers to the cell in the upper left corner, A2 to the cell below this cell, etc.