Require alpha-numeric entry
 

Im 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 Id 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

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:

Im 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 Id 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

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:

Im 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 Id 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

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:

Im 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 Id 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