Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
VLOOKUP WITH ALPHA NUMERIC | Excel Worksheet Functions | |||
Columns are now numeric, not alpha. how to change back? | Excel Discussion (Misc queries) | |||
Search string for alpha or numeric | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) |