Explanation
Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need two conditions:
Logical 1 tests if the input is a number using the ISNUMBER function:
ISNUMBER(B5)
The ISNUMBER function returns TRUE when a value is numeric and FALSE if not.
Logical 2 tests checks that the input doesn’t already exist in the named range “ids”:
COUNTIF(ids,B5)<2
COUNTIF returns a count of the value in B5 inside the named range ids (B5:B9). If the count is less than 2, the logical expression returns TRUE.
If both logical expressions return TRUE, the AND function returns TRUE and validation succeeds:
=AND(TRUE,TRUE) // validation successful
If either logical returns FALSE, data validation fails.
Be aware that numeric input includes dates and times, whole numbers, and decimal values.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5.
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
Explanation
Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
explained in detail here . The result is an array like this:
{"A";"A";"A";"-";"1";"1";"1"}
which goes into MATCH as the lookup value. For the lookup array, we use the named range “allowed”, concatenated to an empty string (""):
allowed&""
The concatenation converts any numbers to strings so that we are matching apples-to-apples. The result is an array like this:
{"A";"B";"C";"1";"2";"3";"-"}
The last argument in MATCH, match_type is set to zero to force an exact match. Because we give MATCH multiple lookup values, we get back an array with multiple results:
{1;1;1;7;4;4;4}
Each number in this array represents a match. In the event a match isn’t found for a character, the array will contain a #N/A error.
Finally, the COUNT function is used to count the numbers in the result array, which is compared to a count of all characters in the cell calculated with the LEN function. When MATCH finds a match for all characters, the counts are equal, the formula returns TRUE, and data validation succeeds. If MATCH doesn’t find a match any character, it returns #N/A instead of a number. In that case, the counts don’t match and data validation fails.
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
