Data Validation

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.
  • "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

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

Updated on January 13, 2022

Was this article helpful?

Related Articles