data validation formula
 

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)

data validation formula
 

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)

data validation formula
 

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)

data validation formula
 

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)

data validation formula
 

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)