Data Validation for text only formula
 

I was given this formula and I am trying to enter it in a cell to only allow
the user to enter text howerver it does not fit. Any suggestions?
=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 for text only formula
 

Kimberly

The formula does not go into a cell.....it goes into the Custom Formula dialog
box in DataValidationCustom

Just CTRL + C the formula from your post then CTRL + V into that dialog box.

NOTE: this formula is written to limit A1 only to text.

Adjust the cell ref if placing in other cells.


Gord Dibben MS Excel MVP


On Thu, 9 Nov 2006 10:26:01 -0800, Kimberly
wrote:

I was given this formula and I am trying to enter it in a cell to only allow
the user to enter text howerver it does not fit. Any suggestions?
=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 for text only formula
 

Here's a sample file with this implemented:

Data Validation.xls 14.0kb

http://cjoint.com/?ljukUJHGNl

Biff

"Kimberly" wrote in message
...
I was given this formula and I am trying to enter it in a cell to only
allow
the user to enter text howerver it does not fit. Any suggestions?
=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 for text only formula
 

Thank you both! Thanks Biff - I didn't know how else to respond to you.

"Biff" wrote:

Here's a sample file with this implemented:

Data Validation.xls 14.0kb

http://cjoint.com/?ljukUJHGNl

Biff

"Kimberly" wrote in message
...
I was given this formula and I am trying to enter it in a cell to only
allow
the user to enter text howerver it does not fit. Any suggestions?
=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 for text only formula
 

You're welcome. I hope we have this straightened out!

Biff

"Kimberly" wrote in message
...
Thank you both! Thanks Biff - I didn't know how else to respond to you.

"Biff" wrote:

Here's a sample file with this implemented:

Data Validation.xls 14.0kb

http://cjoint.com/?ljukUJHGNl

Biff

"Kimberly" wrote in message
...
I was given this formula and I am trying to enter it in a cell to only
allow
the user to enter text howerver it does not fit. Any suggestions?
=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)