I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

What exactly do you mean?

The length of the formula (213 chars) is within the length limit (255
chars). It works just fine on my end.

Try this:

Enter the formula in a cell then copy it and paste it into the validation
refedit box.

Biff

"Kimberly" wrote in message
...
I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

What is preventing you from doing so?

CTRL + c to copy it

Select A1 and DataValidationAllowCustomFormula............CT RL + v to
paste.


Gord Dibben MS Excel MVP



On Tue, 7 Nov 2006 10:47:01 -0800, Kimberly
wrote:

I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

Select your cells:

=NOT(ISNUMBER(A2))

Where A2 is the Activecell.


Regards
Robert McCurdy

"Kimberly" wrote in message ...
I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

Biff's formula should work fine, but as shorter alternative you could
try:

=AND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="a")

which also allows accented characters or for pure text try:

=SUM(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90" ))),"")))=LEN(A1)

Make sure A1 is selected when you enter this in the condition
formatting dialog box.


Biff wrote:

What exactly do you mean?

The length of the formula (213 chars) is within the length limit (255
chars). It works just fine on my end.

Try this:

Enter the formula in a cell then copy it and paste it into the validation
refedit box.

Biff

"Kimberly" wrote in message
...
I am not able to enter this formula into the data validation in order to
allow a user to only enter text. any suggstions?

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT (--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)