Results 1 to 7 of 7

20031205, 00:47 #1
 Join Date
 Mar 2003
 Posts
 174
 Thanks
 0
 Thanked 0 Times in 0 Posts
Customized Data Validation (Excel 2000)
Is it possible to add customized data input validation ? For example, user can only input data in the cell in the following format: "three letters + four digits + two letters" (i.e. BBC3423AB, YZE7431TC ...)

20031205, 02:04 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Customized Data Validation (Excel 2000)
Try adding this formula:
=(MID(a1,1,1)>="A")*(MID(a1,1,1)<="Z")*(MID(a1,2,1 )>="A")*(MID(a1,2,1)<="Z")*(MID(a1,3,1)>="A")*(MID (a1,3,1)<="Z")*(ISNUMBER(VALUE(MID(A1,4,4))))*(MID (a1,8,1)>="A")*(MID(a1,8,1)<="Z")*(MID(a1,9,1)>="A ")*(MID(a1,9,1)<="Z")
The formula must be <256 char so you are near the limits so not too many more conditions can be added
Steve

20031205, 03:18 #3
 Join Date
 Mar 2003
 Posts
 174
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Customized Data Validation (Excel 2000)
Thanks ! This works well. However, what I actually need is "AAAAAAA00" (seven characters plus two numbers). Anyway to get around the 256 character limit ?

20031205, 08:21 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Customized Data Validation (Excel 2000)
Why didn't you say so in your original question? The solution is to put the validation formula in a cell (which may be in a hidden column).
Say that you want to validate cell A1. Put the following formula into cell B1:
=(MID(A1,1,1)>="A")*(MID(A1,1,1)<="Z")*(MID(A1,2,1 )>="A")*(MID(A1,2,1)<="Z")*(MID(A1,3,1)>="A")*(MID (A1,3,1)<="Z")*(ISNUMBER(VALUE(MID(A1,8,2))))*(MID (a1,4,1)>="A")*(MID(A1,4,1)<="Z")*(MID(A1,5,1)>="A ")*(MID(A1,5,1)<="Z")*(MID(A1,6,1)>="A")*(MID(A1,6 ,1)<="Z")*(MID(A1,7,1)>="A")*(MID(A1,7,1)<="Z")*(I SNUMBER(VALUE(MID(A1,8,2))))
and use =B1 as custom validation formula. You could also create a userdefined function:
Function IsValidEntry(strVal) As Boolean
IsValidEntry = (strVal Like "[AZ][AZ][AZ][AZ][AZ][AZ][AZ]##")
End Function
and enter the formula =IsValidEntry(A1) in cell B1, and again use =B1 as custom validation formula.

20031205, 10:22 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Customized Data Validation (Excel 2000)
You got around the issue of not being able to use a custom function in datavalidation with an intermediate value. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
Much nicer than mine, if you don't mind the intermediate.
Steve

20031205, 10:53 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Customized Data Validation (Excel 2000)
I didn't think of it myself  see MSKB article XL97: Cannot Use Custom Function with Data Validation.

20031205, 15:30 #7
 Join Date
 Mar 2003
 Posts
 174
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Customized Data Validation (Excel 2000)
Great ! Many thanks.