Data Validation - Multiple criteria

[Colleen Kayter]

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!

[#2]

@Colleen Kayter I think the instructions on "Prevent duplicates in Ranges of cells"

at https://www.contextures.com/xlDataVal07.html#duprange may help you

[#3 · Accepted solution]

@Colleen Kayter 

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

[#4]

@Jos_Woolley 

Thank you, Jos. You are my hero!

[#5]

@Colleen Kayter 

Nice to know! :)