I need formulas for data validation to limit this information.
1. XX00000
2. 00000000X
3. 000000X
Where XX is any combination of letters (upper or lowercase) and
00000 is a combination of any 5 numbers.
All of these formaulas can not allow special characters.
These can not be array formulas.
It must always be in this format, with this number of digits in total on each
By imputting a specific formula data validation this will only allow a user to type in this specific criteria.
Hi Madams, welcome to the forum.
Rather than using a convoluted Data Validation formula, perhaps a worksheet change event (macro) would be better suited? This code, which would be added to the code module for the worksheet on which you're entering data, will check column A for any changes as you make them, and determine whether or not the entry is valid. It looks for the three formats you specified above (XX00000, 00000000X and 000000X). You can adjust the code to suit your actual data layout.
This could also be done using Regular Expressions (regex), but I'm not very good at using those just yet.
Please Login or Register to view this content.
where would I specify the specific cells within the macros. Each 1, 2, and 3 have different cells. Im not familiar with macro, I really appreciate all of your help.
I had an issue similar to this involving lot numbers in the format 0000XX. I had the user enter the numbers and letters in separate cells, and then concatenate (and capitalize) them after validation.
For the numbers, I am validating for a whole number from 1-9999. The cell's custom format is 0000.
For the letters, I used the following custom formula to validate that only two letters may be entered.
The formula was provided by circledchicken, on another forum.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks