Custom Data Validation number & a letter from named rang

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
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

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,491
Members
412,669
Latest member
nickyon1
Top