alpha numeric data validation for excel
"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
This seems to work:
=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),
--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4
...
Or avoiding the volatile INDIRECT function but taking advantage of
ASCII encoding,
=AND(LEN(x)=7,ABS(CODE(MID(x,{1;2},1))-77.5)<13,COUNT(-MID(x,
{3;4;5;6;7},1))=5)
Nice one, Harlan.
I should've realized we could use array constants rather than
ROW(INDIRECT(...)) since it's only a few characters.
About the volatile INDIRECT...
"T. Valko" wrote...
It appears that a formula with a volatile function used
as a validation rule doesn't "act" volatile. In a test file
I entered the validation rule using INDIRECT. Closed
the file, opened the file, didn't do anything at all, then
closed the file without Excel asking if I wanted to save
changes ( a telltale sign that a volatile function is in use)
Any thoughts on that?
--
Biff
Microsoft Excel MVP
|