Data Entry Mask

  • Thread starter Thread starter Ashish
  • Start date Start date
A

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?
 
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)
 
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
 
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)
 
Back
Top