Visyond allows data validation using the function DATAVALID.
Description
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.
Syntax
DATAVALID (value, type, validation_rule)
- value is any value to validate
- 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 are valid (note, that1.0
is a valid whole number)"decimal"
– only decimal numbers are valid (note, that1
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 are comma separated values that, based on the type, provide the rule to validate the value.
- 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.
- For numerical-based validations, we can have:
- 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))
:
You can use DATAVALID as any other function, therefore they can be nested, concatenated, etc. This offer a greater level of flexibility compared to the typical spreadsheet data validation option.
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