May 26 2020 03:33 PM
I am setting up a log sheet in Excel for product lot numbers. The format is stipulated by the customer, so that cannot be changed.
The format must be as follows:
2 numbers + 1 alpha + 2 numbers + 1 alpha (always E) + 2 numbers. (##?##E##)
AND the resulting string must be UNIQUE.
I've read through everything I can find on data validation and can't seem to come up with the formula that gets this done. I'm really missing my Access tools.
Thank you, community for your help with this puzzle!
May 26 2020 04:36 PM
@Colleen Kayter I think the instructions on "Prevent duplicates in Ranges of cells"
at https://www.contextures.com/xlDataVal07.html#duprange may help you
May 26 2020 11:13 PM - edited May 26 2020 11:25 PM
Solution
Hi,
Assuming the entries in question are all in column A and that the first cell to which you wish to apply the validation is cell A1, then, with the active cell somewhere in row 1, go to Name Manager (Formulas tab) and define:
Name: MyValidation
Refers to: =AND(LEN($A1)=8,1-ISERR(0+MID($A1,{1,2,4,5,7,8},1)),EXACT(MID($A1,6,1),"E"),ABS(77.5-CODE(MID($A1,3,1)))<13,COUNTIF($A:$A,$A1)=1)
After which you can apply data validation to cell A1 choosing 'Custom' in the Allow box and entering the following in the Formula box:
=MyValidation
Make sure you also uncheck the Ignore blank box.
A small point, but if you are intending to use the Circle Invalid Data tool to audit incorrect entries, just be aware that any cells to which you extend the data validation and which are blank will be circled as invalid. If this is not desirable I can provide you with an amended solution.
N.B. You didn't say, but I presumed that the two alphabetic characters both have to be upper case. The current validation disallows lower case.
Regards
May 27 2020 12:02 PM
May 26 2020 11:13 PM - edited May 26 2020 11:25 PM
Solution
Hi,
Assuming the entries in question are all in column A and that the first cell to which you wish to apply the validation is cell A1, then, with the active cell somewhere in row 1, go to Name Manager (Formulas tab) and define:
Name: MyValidation
Refers to: =AND(LEN($A1)=8,1-ISERR(0+MID($A1,{1,2,4,5,7,8},1)),EXACT(MID($A1,6,1),"E"),ABS(77.5-CODE(MID($A1,3,1)))<13,COUNTIF($A:$A,$A1)=1)
After which you can apply data validation to cell A1 choosing 'Custom' in the Allow box and entering the following in the Formula box:
=MyValidation
Make sure you also uncheck the Ignore blank box.
A small point, but if you are intending to use the Circle Invalid Data tool to audit incorrect entries, just be aware that any cells to which you extend the data validation and which are blank will be circled as invalid. If this is not desirable I can provide you with an amended solution.
N.B. You didn't say, but I presumed that the two alphabetic characters both have to be upper case. The current validation disallows lower case.
Regards