Doh... of course it needed a LEN test (can't believe I missed that).

So then, I guess INDIRECT is indirect only when used directly.<g

Yes, using the automatic array process is definitely better. Of course, I
tested my logic out on the grid directly and used SUMPRODUCT during testing
to avoid hitting the Ctrl+Shift+Enter (just lazy on my part); then, because
my wife wanted to use the computer, I rushed posting my findings without
even thinking about the array processing part of it.

Thanks for catching all the issue I missed.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7

it does get rid of the volatile function calls (I wonder if that matter in
a validation formula)

Well, it leaves the formula vulnerable to row insertions and it accepts
more than 7 characters. 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)

We can shorten it by a few more keystrokes while at the same time adding a
length test since a formula entered in a refedit is automatically
processed as an array:

=SUM(--(LEN(A2)=7),--(ABS(CODE(MID(A2,ROW($1:$2),1))-77.5)<=12.5),COUNT(-MID(A2,ROW($3:$7),1)))=8

So, as long as you don't insert new rows (in certain places) that looks
pretty good.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Okay, this is only a little shorter, but it does get rid of the volatile
function calls (I wonder if that matter in a validation formula)...

=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
That allows more than 7 characters:

AB12345xxxxxx
AB1234567890

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

--
Biff
Microsoft Excel MVP


"vezerid" wrote in message
...
With the validated cell being A2:

=PRODUCT(--(CODE(MID(A2,ROW(INDIRECT("1:2")),1))=65))*PRODUC T(--(CODE
(MID(A2,ROW(INDIRECT("1:2")),1))<=90))*PRODUCT(--ISNUMBER(--MID(A2,ROW
(INDIRECT("3:7")),1)))

A bit complex but ensures that no . or E will be used in the last five
digits.

HTH
Kostis Vezerides

On Nov 28, 7:23 pm, wrote:
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?