Data Validation in Visyond

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.

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, that 1.0 is a valid whole number)
  • "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 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:
    1. 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)
    2. “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