Custom Data Validation number & a letter from named rang

[Joyner]

Hi, I have searched but not found an answer to my particular situation. Can custom data validation do this? I think the first and last parts may be easy, but the letter using a named range may be difficult?

I want to use data validation to only allow entry of 13 characters, the first 4 are numbers (the year), the last 8 are numbers, and the 5th character is a letter that I would like to be limited to a named range (Team).

Is this possible.

Thank you for any help....Dean

 

[Oaktree]

Try this:

Allow custom, where the formula is:

=AND(ISNUMBER(LEFT(A1,4)+0),ISNUMBER(RIGHT(A1,8)+0),LEN(A1)=13,ISNUMBER(MATCH(MID(A1,5,1),team,0)))

 

[Joyner]

Oaktree,

That works great. Thank you for the help. I could not figure something like that out. But now that I have seen your solution I should be able to adapt it to any similar problem.

Thanks again....Dean