Explanation
Data validation rules are triggered when a user adds or changes a cell value.
When a custom formula returns TRUE, validation passes and the input is accepted.
When a formula returns FALSE, validation fails and the input is rejected with a popup message.
In this case, we have previously defined thenamed range"xlist" as D5:D11.
This range holds characters that are not allowed.
Next, the COUNT function returns the count of all numbers in the array.
When the array contains no numbers (i.e.
no restricted characters) COUNT returns zero, the expression returns TRUE, and data validation succeeds.
However, When the array contains no numbers (i.e.
The characters that appear in the named rangexlistcan be customized fit requirements.
When the text is not found, FIND returns a #VALUE error.
COUNT Function
The Excel COUNT function returns a count of values that are numbers.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
Empty cells and text values are ignored….
