Validation Rule: Must be between [...]

#EN #Excel #Validation

Updated over a week ago

Validation rule to use

Use the sentence Must be between [...] to set a field to be triggered when a value is not between two targeted values.

The comparison order is important. The lowest value must be looked at first, then the highest one.
Functional example: Must be between -2 and 10
Non-functional example: Must be between 10 and -2 

Validation rules available

Must be between Value_1 and Value_2

Allows to set a numeric field invalid if the entered value is not between two targeted values.

Alternative rules:

  • Must be between Value_1 and Value_1 (if you wish to target the same value and you need your field to be equal to an exact value)

  • Invalid when not between Value_1 and Value_1

  • Invalid when not between Value_1 and Value_2

Must be between Field_1 and Field_2

Allows to set a numeric field invalid if the entered value is not between two values in two targeted fields.

Alternative rules:

  • Must be between Field_1 and Field_1 (if you wish to target the same value and you need your field to be equal to an exact value)

  • Invalid when not between Field_1 and Field_1

  • Invalid when not between Field_1 and Field_2

Must be between Field and Value

Allows to set a numeric field invalid if the entered value is not between a value in a targeted field and a targeted value.

Alternative rules:

  • Must be between Value and Field

  • Invalid when not between Field and Value

  • Invalid when not between Value and Field

Keywords available with this validation rule

  • Numeric (up to 7 numbers)

  • Text (up to 12 numbers, no text, no symbols)

Did this answer your question?