Data Validation - Multiple criteria

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