Forum Discussion
Data Validation - Multiple criteria
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
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
