Require alpha-numeric entry

I’m creating a spreadsheet for data collection from various employees and am
trying to make it as “goof-proof” as possible.

The first column must not be blank, must be unique, and requires one (1)
lower case text character (currently a, c, e, or f) followed by any six (6)
numbers. i.e. e123456. This will be used to validate against a master
database later.

I have a conditional format set to show if the cell is duplicated using
=COUNTIF($A$2:$A$1000,A2)>1. The format for this condition changes the color
of both duplicate cells to red so they can correct it.

I have validation set to require exactly seven (7) characters. The “ignore
blank” checkbox is unchecked. The input and stop messages gives them an
example.

This still allows any entry that is seven characters, numeric or text (upper
or lower case) or any combination.

What I’d like to do is test their entry for our exact requirements and if
possible, stop them from moving on until it is correct. Can this be done
using worksheet functions or does it require a VBA script?

I reallize this would probably be easier in MS Access, but not all have the
program and many wouldn't be able to use it.

TIA.

Dana Scott
dascott@laisd.org

Hi Dana,

Try to apply this formula in Validation/Specific

=AND(OR(LOWER(LEFT(A1,1))="a",LOWER(LEFT(A1,1))="c",LOWER(LEFT(A1,1))="e",LOWER(LEFT(A1,1))="f"), LEN(A1)=7, ISNUMBER(VALUE(MID(A1,2,6))))

Regards,
Stefi

„Dana” ezt �*rta:

> I’m creating a spreadsheet for data collection from various employees and am
> trying to make it as “goof-proof” as possible.
>
> The first column must not be blank, must be unique, and requires one (1)
> lower case text character (currently a, c, e, or f) followed by any six (6)
> numbers. i.e. e123456. This will be used to validate against a master
> database later.
>
> I have a conditional format set to show if the cell is duplicated using
> =COUNTIF($A$2:$A$1000,A2)>1. The format for this condition changes the color
> of both duplicate cells to red so they can correct it.
>
> I have validation set to require exactly seven (7) characters. The “ignore
> blank” checkbox is unchecked. The input and stop messages gives them an
> example.
>
> This still allows any entry that is seven characters, numeric or text (upper
> or lower case) or any combination.
>
> What I’d like to do is test their entry for our exact requirements and if
> possible, stop them from moving on until it is correct. Can this be done
> using worksheet functions or does it require a VBA script?
>
> I reallize this would probably be easier in MS Access, but not all have the
> program and many wouldn't be able to use it.
>
> TIA.
>
> Dana Scott
> dascott@laisd.org
>

Stefi,

Thanks for the prompt reply.

In Validation I changed the Allow: box to Custom. Entered the formula in
the Formula: box changing the 'A1' to 'A2' to allow for my titles.

It worked to require 7 digits and to require the a, c, e, or f. But it
allowed both upper case and lower case.

Any further suggestions? In case it matters, I'm using Excel 2003 SP1.

Regards,

Dana


"Dana" wrote:

> I’m creating a spreadsheet for data collection from various employees and am
> trying to make it as “goof-proof” as possible.
>
> The first column must not be blank, must be unique, and requires one (1)
> lower case text character (currently a, c, e, or f) followed by any six (6)
> numbers. i.e. e123456. This will be used to validate against a master
> database later.
>
> I have a conditional format set to show if the cell is duplicated using
> =COUNTIF($A$2:$A$1000,A2)>1. The format for this condition changes the color
> of both duplicate cells to red so they can correct it.
>
> I have validation set to require exactly seven (7) characters. The “ignore
> blank” checkbox is unchecked. The input and stop messages gives them an
> example.
>
> This still allows any entry that is seven characters, numeric or text (upper
> or lower case) or any combination.
>
> What I’d like to do is test their entry for our exact requirements and if
> possible, stop them from moving on until it is correct. Can this be done
> using worksheet functions or does it require a VBA script?
>
> I reallize this would probably be easier in MS Access, but not all have the
> program and many wouldn't be able to use it.
>
> TIA.
>
> Dana Scott
> dascott@laisd.org
>

Hi Dana,

This will differentiate upper and lower case:

=AND(OR(ASC(LEFT(A1,1))=97,ASC(LEFT(A1,1))=99,ASC(LEFT(A1,1))=101,ASC(LEFT(A1,1))=102), LEN(A1)=7, ISNUMBER(VALUE(MID(A1,2,6))))

Regards,
Stefi

„Dana” ezt �*rta:

> Stefi,
>
> Thanks for the prompt reply.
>
> In Validation I changed the Allow: box to Custom. Entered the formula in
> the Formula: box changing the 'A1' to 'A2' to allow for my titles.
>
> It worked to require 7 digits and to require the a, c, e, or f. But it
> allowed both upper case and lower case.
>
> Any further suggestions? In case it matters, I'm using Excel 2003 SP1.
>
> Regards,
>
> Dana
>
>
> "Dana" wrote:
>
> > I’m creating a spreadsheet for data collection from various employees and am
> > trying to make it as “goof-proof” as possible.
> >
> > The first column must not be blank, must be unique, and requires one (1)
> > lower case text character (currently a, c, e, or f) followed by any six (6)
> > numbers. i.e. e123456. This will be used to validate against a master
> > database later.
> >
> > I have a conditional format set to show if the cell is duplicated using
> > =COUNTIF($A$2:$A$1000,A2)>1. The format for this condition changes the color
> > of both duplicate cells to red so they can correct it.
> >
> > I have validation set to require exactly seven (7) characters. The “ignore
> > blank” checkbox is unchecked. The input and stop messages gives them an
> > example.
> >
> > This still allows any entry that is seven characters, numeric or text (upper
> > or lower case) or any combination.
> >
> > What I’d like to do is test their entry for our exact requirements and if
> > possible, stop them from moving on until it is correct. Can this be done
> > using worksheet functions or does it require a VBA script?
> >
> > I reallize this would probably be easier in MS Access, but not all have the
> > program and many wouldn't be able to use it.
> >
> > TIA.
> >
> > Dana Scott
> > dascott@laisd.org
> >