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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,052
Office Version
  1. 365
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,500
Messages
5,832,072
Members
430,110
Latest member
Chyke_mxl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top