Data validation problem

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
 
G

Guest

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
 
O

okaizawa

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)
 
T

Tom Ogilvy

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...
 
O

okaizawa

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
Full proof UK postcode validation 15
return specific character read from right to left 0
Separate first and last name in two columns 3
Data Validation - Accept Only Certain Numbers/Characters 7
Combining data from multiple rows 2
analyzing data with VBA 4
Make easier to use formula's / have better / useful examples 1

Top