PDA

View Full Version : Solved: Data Validation based on multiple criteria



kellyhell
10-04-2010, 04:18 AM
I am attempting to set validation on a cell based on 2 criteria.
The user can either input data into a cell but it must meet the following format:-

AB123456C (i.e. 2 letters, 6 numbers then a letter) or 12/03/1967 (i.e a date of birth using the "/" as a separator.

Any other format is not allowed as a VLOOKUP is used based on what is input to this cell.

I have looked at using a formula in Data>Validation and cannot work it out.

Is there such a formula or any VBA which would do it?
TIA

Bob Phillips
10-04-2010, 05:21 AM
Dates do not contain the / characters and so on, that is just a view. They are just numbers, how they are input depends upon the format.

So, all you need is

=OR(AND(LEN(C12)=9,NOT(ISNUMBER(--LEFT(C12,2))),ISNUMBER(--MID(C12,3,6)),NOT(ISNUMBER(--RIGHT(C12,1)))),AND(ISNUMBER(C12)))