Visyond allows data validation using the function DATAVALID.

You can use data validation to restrict the type of data or the values that users enter into a cell or to create drop-down lists.

You can use DATAVALID as any other function, therefore they can be nested, concatenated, etc. This offers a greater level of flexibility compared to the typical spreadsheet data validation options.

Only a new formula (any user input beginning with ‘=’) will overwrite a formula containing DATAVALID. Other inputs (e.g., numbers, strings, booleans) will be validated against the existing formula.

You can always delete a formula containing DATAVALID (if you have permission to delete that Case).

Syntax

DATAVALID (value, type, validation_rules)

Value

value is any value to validate

Type

type is a string containing type of validation (“list”, “number”, etc.); the following types are supported:

• "list" – only values from a list are valid.
• "whole" – only whole numbers
• "decimal" – only decimal numbers are valid (note, that 1 is a valid decimal number)
• "date" – only dates are valid
• "time" – only time are valid
• "text_len" – only string of specified length are valid

Validation_Rules

validation_rules are comma separated values that, based on the type, provide the rule to validate the value.

List

A list can be specified as both static (an array of comma separated values) or a range; when the type is list, validation_rule will provide the values to show in a dropdown menu.

Numerical-based validations

Operand followed by a value (e.g, =DATAVALID (5, "whole", ">", 2)); any additional comma separated entries will be ignored:

• ">" (greater than)
• "<" (less than)
• ">=" (greater than or equal to)
• "<=" (less than or equal to)
• "=" (equal to)
• "<>" (not equal to)

“between” or “not_between” followed by 2 values(e.g, =DATAVALID (5, "whole", "between", 2, 7)):

Examples

=DATAVALID (“UK”, “list”, “Germany”, “Italy”, “UK”, “USA”)

will return UK

=DATAVALID (“Finland”, “list”, “Germany”, “Italy”, “UK”, “USA”)

will return error #VALID! since Finland is not in the validation list

=DATAVALID (5, “whole”, “between”, 2 , 4)

will return error #VALID! since 5 is not less than 4

Let

• A1 contain Germany
• A2 contain UK
• A3 contain USA

= DATAVALID (“UK”, “list”, A1:A3)

will return UK as a validated name

= DATAVALID (“Germany”, “list”, A1:A3) & DATAVALID (5, “whole”, “>2”)

will return Germany5