Data Entry Mask

[Ashish]

I have a spreadsheet in which people are going to send me
information. One of the field that I want is the Sales
Order number. I want them to always use the format of
three alpha characters and seven numeric characters for
this field. Where should I create such a mask that they
can only enter the data that is like "AAA1234567" (three
alpha characters and seven numeric characters?

 

[Frank Kabel]

Hi
use 'Data - validation' and enter the following custom formula (e.g.
for cell A1):
=AND(SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("1:3")),1))))=0,SUMPR
ODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("4:10")),1))))=7)

 

[Guest]

The Validation Rule errors out because it doesn't know
what range I'm referring to...it's saying that the range
specified is inaccurate.

Any other suggestions?

Ashish

 

[Dave Peterson]

Try this version of Frank's formula (you got hit by a line break):

=AND(SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("1:3")),1))))=0,
SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("4:10")),1))))=7)

Copy both physical lines (one logical line) and ctrl-v in the formula box (in
the data|validation dialog)