@ > Home > Contents > Array formula within Data Validition Excel 97+

Problem: 

A1 needs validation of an entry containing the characters a to z or A to Z exclusively. Foreign letters, kommas, spaces or all other characters be not allowed. Validation shall take place for ALL, not only the FIRST character.

Note: Normally, Data Validition does not accept explicit arrays, as {=SUM(...)} or {1,2}.

Solution: 

A1: Data Validition Custom Formula:
=SUMPRODUCT(--(ABS(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-77,5)<13))=LEN(A1)

So, implicit arrays do work.

The formula has reached its maximum nesting level of functions (8 before xl2007, which allows 64).