Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dana
 
Posts: n/a
Default 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


  #2   Report Post  
Stefi
 
Posts: n/a
Default

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


  #3   Report Post  
Dana
 
Posts: n/a
Default

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


  #4   Report Post  
Stefi
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP WITH ALPHA NUMERIC JACOB Excel Worksheet Functions 14 June 16th 05 05:10 PM
Columns are now numeric, not alpha. how to change back? samriepe Excel Discussion (Misc queries) 1 June 9th 05 04:17 PM
Search string for alpha or numeric David Excel Worksheet Functions 4 June 8th 05 06:19 PM
The colums changed from alpha to numeric how do you make it alpha worldmade Excel Discussion (Misc queries) 2 May 26th 05 04:44 PM
Alpha & Numeric Counts in Excel Programmer wanna be Excel Discussion (Misc queries) 3 April 5th 05 12:12 PM


All times are GMT +1. The time now is 10:45 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"