Data validation problem

  • Thread starter Thread starter broogle
  • Start date Start date
B

broogle

Hi, I'm having dificulties fitting the formula in data validation.

The condition should be :

1. A-TFG-1234(a or ab)
2. B-TFG-1234(a or ab)
or
3. ABC1234 - (TFG1234 is not allowed)

Note:

For No.1 and No. 2 :
"A-TFG-" or " "B-TFG-" is mandatory.
1234 is numeric only
(a or ab) any alphabets (lowercase or uppercase are allowed)
minimum 10 character, maximum 12 character
sample : A-TFG-4675, B-TFG-6544KL or B-TFG2344G
Click to expand...

For No. 3
ABC is (a|A..z|Z only) but TFG is not allowed
1234 is a number
max 7 character
sample : BTU3456, wrt2231
sample : TFG2245 (is not allowed)
Click to expand...

Below is my code:

=OR(AND(OR(LEFT(C10,6)="A-TFG-",LEFT(C10,6)="B-TFG-"),ISNUMBER(MID(C10,7,4)*1),AND(LEN(C10)>=10,NOT(ISNUMBER(MID(C10,11,1)*1)))),(AND(ISNUMBER(RIGHT(C10,4)*1),NOT(ISNUMBER(LEFT(C10,1)*1)),NOT(ISNUMBER(MID(C10,3,1)*1)),LEN(C10)=7,LEFT(C10,3)<>"TFG")))

still not cover everything due to the lack of space.

Anybody can simplify this code?
Please help.

Thank you
 
Hi,
Try this UDF and change function return value to suit your needs

Sub test()
MsgBox DataVal("A-TFG-123ab")
End Sub

or

A1="abc1234"
B!= "=dataval(A1)"

Function DataVal(indata)
Dim strdata As String, i As Integer, valid As Boolean

strdata = indata
valid = True

Select Case Left(strdata, 6)

Case Is = "A-TFG-", "B-TFG-"
If Len(strdata) >= 10 Then
For i = 7 To 10 ' Check 0 to 9
If Asc(UCase(Mid(strdata, i, 1))) < 48 Or Asc(UCase(Mid(strdata, i,
1))) > 57 Then
valid = False
End If
Next i
If Len(strdata) > 10 And Len(strdata) <= 12 Then
For i = 11 To Len(strdata) ' Check A to Z
If Asc(UCase(Mid(strdata, i, 1))) < 65 Or
Asc(UCase(Mid(strdata, i, 1))) > 90 Then
valid = False
End If
Next i
Else
valid = False
End If
Else
valid = False
End If

Case Else

If Len(strdata) = 7 Then
For i = 1 To 3 ' Check A to Z
If Asc(UCase(Mid(strdata, i, 1))) < 65 Or Asc(UCase(Mid(strdata, i,
1))) > 90 Then
valid = False
End If
Next i
For i = 4 To 7 ' Check 0 to 9
If Asc(UCase(Mid(strdata, i, 1))) < 48 Or Asc(UCase(Mid(strdata, i,
1))) > 57 Then
valid = False
End If
Next i
If UCase(Left(strdata, 3)) = "TFG" Then
valid = False
End If

Else
valid = False
End If

End Select
If Not valid Then
DataVal = "Invalid data"
Else
DataVal = "OK"
End If
End Function


HTH
 
Hi,
it seems that array formula cannot work well in Data-Validation.
i suppose UDF is better than a long logn formula.
i don't know any good way to use a UDF in Data-Varidation,
but the following seems to work on my PC. (excel 2000)

1. make a UDF in a standard module like this:

'Module1
Function CheckFormat(Cell1 As Range, Cell2 As Range) As Range
Dim s As String
On Error GoTo ErrorHandler
Set CheckFormat = Cell1
s = UCase(Cell1.Value)
If s Like "A-TFG-####*" Or s Like "B-TFG-####*" Then
s = Mid(s, 11)
If s = "" Or s Like "[A-Z]" Or s Like "[A-Z][A-Z]" Then
Exit Function
End If
ElseIf s Like "[A-Z][A-Z][A-Z]####" And (Not s Like "TFG*") Then
Exit Function
End If
ErrorHandler:
Set CheckFormat = Cell2
End Function


2. select C10 and do Insert > Name > Define
Names in workbook:
Check1
Refers to:
=CheckFormat(!C10,!$G$1)

3. put =NA() in G1. (used as a working area)

4. select C10 and do Data > Validation > Settings
Allow:
Custom
Formula:
=Check1=C10

or

Allow:
List
Source:
=Check1

[ ] In-cell dropdown(off)
 
What array formula were you refering to?

--
Regards,
Tom Ogilvy

okaizawa said:
Hi,
it seems that array formula cannot work well in Data-Validation.
i suppose UDF is better than a long logn formula.
i don't know any good way to use a UDF in Data-Varidation,
but the following seems to work on my PC. (excel 2000)

1. make a UDF in a standard module like this:

'Module1
Function CheckFormat(Cell1 As Range, Cell2 As Range) As Range
Dim s As String
On Error GoTo ErrorHandler
Set CheckFormat = Cell1
s = UCase(Cell1.Value)
If s Like "A-TFG-####*" Or s Like "B-TFG-####*" Then
s = Mid(s, 11)
If s = "" Or s Like "[A-Z]" Or s Like "[A-Z][A-Z]" Then
Exit Function
End If
ElseIf s Like "[A-Z][A-Z][A-Z]####" And (Not s Like "TFG*") Then
Exit Function
End If
ErrorHandler:
Set CheckFormat = Cell2
End Function


2. select C10 and do Insert > Name > Define
Names in workbook:
Check1
Refers to:
=CheckFormat(!C10,!$G$1)

3. put =NA() in G1. (used as a working area)

4. select C10 and do Data > Validation > Settings
Allow:
Custom
Formula:
=Check1=C10

or

Allow:
List
Source:
=Check1

[ ] In-cell dropdown(off)

--
HTH,

okaizawa

Hi, I'm having dificulties fitting the formula in data validation.

The condition should be :

1. A-TFG-1234(a or ab)
2. B-TFG-1234(a or ab)
or
3. ABC1234 - (TFG1234 is not allowed)

Note:

For No.1 and No. 2 :



For No. 3



Below is my code:
Click to expand...
=OR(AND(OR(LEFT(C10,6)="A-TFG-",LEFT(C10,6)="B-TFG-"),ISNUMBER(MID(C10,7,4)*
1),AND(LEN(C10)>=10,NOT(ISNUMBER(MID(C10,11,1)*1)))),(AND(ISNUMBER(RIGHT(C10
,4)*1),NOT(ISNUMBER(LEFT(C10,1)*1)),NOT(ISNUMBER(MID(C10,3,1)*1)),LEN(C10)=7
still not cover everything due to the lack of space.

Anybody can simplify this code?
Please help.

Thank you
Click to expand...
Click to expand...
 
Tom said:
What array formula were you refering to?
Click to expand...

sorry for my insufficient and strange explanation.
it is my array formula that has not worked in Data-Validation
like this:

=OR(LEFT(C10,6)={"A-TFG-","B-TFG-"})*AND(ISNUMBER(--MID(C10,{7,8,9,10},1)))
*AND(ABS(CODE(UPPER(MID(C10,{11,12},1)&"a"))-77.5)<13)*(LEN(C10)<=12)
+AND(ABS(CODE(UPPER(MID(C10,{1,2,3},1)))-77.5)<13)*(LEFT(C10,3)<>"TFG")
*AND(ISNUMBER(--MID(C10,{4,5,6,7},1)))*(LEN(C10)=7)

or a little bit shorter

=OR(LEFT(C10,6)={"A-TFG-","B-TFG-"})*AND(ISNUMBER(--MID(C10,{7,8,9,10},1))
,ABS(CODE(UPPER(MID(C10,{11,12},1)&"a"))-77.5)<13,LEN(C10)<13)
+AND(ABS(CODE(UPPER(MID(C10,{1,2,3},1)))-77.5)<13,LEFT(C10,3)<>"TFG"
,ISNUMBER(--MID(C10,{4,5,6,7},1)),LEN(C10)=7)

i think that it is necessary to test characters one by one.
without array, a long formula will be needed.

=((LEFT(C10,6)="A-TFG-")+(LEFT(C10,6)="B-TFG-"))
*ISNUMBER(--MID(C10,7,1))*ISNUMBER(--MID(C10,8,1))
*ISNUMBER(--MID(C10,9,1))*ISNUMBER(--MID(C10,10,1))
*(ABS(CODE(UPPER(MID(C10,11,1)&"a"))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,12,1)&"a"))-77.5)<13)
*(LEN(C10)<=12)
+(ABS(CODE(UPPER(MID(C10,1,1)))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,2,1)))-77.5)<13)
*(ABS(CODE(UPPER(MID(C10,3,1)))-77.5)<13)
*(LEFT(C10,3)<>"TFG")
*ISNUMBER(--MID(C10,4,1))*ISNUMBER(--MID(C10,5,1))
*ISNUMBER(--MID(C10,6,1))*ISNUMBER(--MID(C10,7,1))
*(LEN(C10)=7)

being divided into multiple names, this will work in Data-Validation
instead of a UDF. but it is more difficult and time$B!>(Bconsuming.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Validation Question 4
Data validation, mandatory and restricted field length for excel vbs 10
data validation formula 4
Full proof UK postcode validation 15
Data Validation for text only formula 4
Separate first and last name in two columns 3
Data Validation - Accept Only Certain Numbers/Characters 7
return specific character read from right to left 0

Back
Top