Conditional validation

  • Thread starter Thread starter Les Isaacs
  • Start date Start date
L

Les Isaacs

Hello All

I need to validate data being entered in row T according to the entry in the
same row in column S, i.e.

if S1=1 then T1 must be 1, 2 or 3
if S1=2 then T1 must be 2, 3, 4 or 5
if S1=3 then T1 must be 3, 4 or 5
if S1=4 then T1 must be 3, 5 or 6
etc.

There is no mathematical relationship betwen the S1 value and the valid T1
values.
S1 must be between 1 and 15, so validation of S1 is simple, but I have tried
to get the conditional validation of T1 but just can't get the syntax right!

Hope soneone can help.

Many thanks
Les
 
Oops - that should have been "I need to validate data being entered in
COLUMN T ..."
Les
 
=OR(AND(S1=1,T1>=1,T1<=3),AND(S1=2,T1>=2,T1<=5),AND(S1=3,T1>=3,T1<=5),AND(S1=4,T1>=3,T1<=6,T1<>4))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob

I hardly dared ask you or Rick to look at this one, hence the new thread:
but many thanks for the answer.
And what's more, unlike the postcode validation code, I understand it (and
so can now adapt it to suit my more general requirement)!

Thanks again
Les
 
Well, it is a bit simpler Les!

BTW I bowed out of that thread because Rick cam back and has spent more time
on it in the past and understood the rules better (I just spotted a logic
flaw in his original code), and I don't want to confuse that thread more,
but I did look up current postcodes and see that only AB and AL are valid.
Do you really think that they may they might add say AC? I had always
thought that the regions (or whatever they call them) were fixed, and any
extra housing/towns would get a sub id within the main region.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob

I honestly don't know whether, say, AC will ever get used, but as the
'rules' allow it I think it would be safest to allow it in my data. The risk
of someone complaining that I didn't allow their (AC...) postcode is greater
than the risk of my allowing a valid but unassigned postcode. I am curious
though where you found the list of assigned postcodes?

Les
 
Rick

Thanks for the reference to the list of assigned postcodes. I was facinated
to see Santa's!!
And thanks for your latest revised function - which is perfect (see my last
post in the other thread)!
Les
 
Les,

I found it here http://www.evoxfacilities.co.uk/evoxar.html

and here http://www.evoxfacilities.co.uk/pcdarea.htm

If AC did ever get used, I think it would be easy to change Rick's code to
accommodate. The part

Parts(0) Like "A[0-9BL]#*"

changes to

Parts(0) Like "A[0-9BCL]#*"

and you have to ask whether you validate for valid current postcodes or
valid possible postcodes. If they were rapidly changing, maybe the latter,
but as they don't, I think the former.

BTW, looked at your website. If you ever need any Excel/VBA consultancy ....
:-)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Rick,

I have looked at your code and can't see why you do all those Parts(0)
checks up-front when you then check them all again later.

Isn't this version (BTW I have changed the multiple IF test to Select Case
to avoid the lack of short-circuiting) working just as well, or am I missing
something?

Function ValidPostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String

PostCode = UCase$(PostCode)
Parts = Split(PostCode, " ")

'check the two abnormal possibilities
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Then

ValidPostCode = True
'check second part doesn't use Q, V or X in first letter
ElseIf Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" Then

'depending upon first letter, validate first part
Select Case Left$(Parts(0), 1)

Case "A": ValidPostCode = Parts(0) Like "A[0-9BL]#*"
Case "B": ValidPostCode = Parts(0) Like "B[0-9ABDHLNRST]#*"
Case "C": ValidPostCode = Parts(0) Like "C[0-9ABFHMORTVW]#*"
Case "D": ValidPostCode = Parts(0) Like "D[0-9ADEGHLNTY]#*"
Case "E": ValidPostCode = Parts(0) Like "E[0-9CHNX]#*"
Case "F": ValidPostCode = Parts(0) Like "F[0-9KY]#*"
Case "G": ValidPostCode = Parts(0) Like "G[0-9LU]#*"
Case "H": ValidPostCode = Parts(0) Like "H[0-9ADGPRSUX]#*"
Case "I": ValidPostCode = Parts(0) Like "I[0-9GPV]#*"
Case "J": ValidPostCode = Parts(0) Like "K[0-9ATWY]#*"
Case "K": ValidPostCode = Parts(0) Like "J##"
Case "L": ValidPostCode = Parts(0) Like "L[0-9ADELNSU]#*"
Case "M": ValidPostCode = Parts(0) Like "M[0-9EKL]#*"
Case "N": ValidPostCode = Parts(0) Like "N[0-9EGNPRW]#*"
Case "O": ValidPostCode = Parts(0) Like "O[0-9LX]#*"
Case "P": ValidPostCode = Parts(0) Like "P[0-9AEHLOR]#*"
Case "R": ValidPostCode = Parts(0) Like "R[0-9GHM]#*"
Case "R": ValidPostCode = Parts(0) Like "S[0-9AEGKLMNOPRSTWY]#*"
Case "T": ValidPostCode = Parts(0) Like "T[0-9ADFNQRSW]#*"
Case "U": ValidPostCode = Parts(0) Like "U[0-9B]#*"
Case "W": ValidPostCode = Parts(0) Like "W[0-9ACDFNRSV]#*"
Case "Y": ValidPostCode = Parts(0) Like "Y[0-9O]#*"
Case "Z": ValidPostCode = Parts(0) Like "Z[0-9E]#*"
End Select
End If
End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob

Thanks for these references.

As mentioned earlier I'm going to stick with validating possible postcodes
as opposed to current postcodes as the tighter restriction is more complex
(!) and more likely to lead to a false rejection - compared to the risk of
the looser restriction leading to a false acceptance.

On the consultancy front, I'll certainly bear your offer in mind if and when
the need arises. The programme we use is an MS Access application (Access97
in fact!), but I assume that Access's VBA is the same as Excel's?

Les


Bob Phillips said:
Les,

I found it here http://www.evoxfacilities.co.uk/evoxar.html

and here http://www.evoxfacilities.co.uk/pcdarea.htm

If AC did ever get used, I think it would be easy to change Rick's code to
accommodate. The part

Parts(0) Like "A[0-9BL]#*"

changes to

Parts(0) Like "A[0-9BCL]#*"

and you have to ask whether you validate for valid current postcodes or
valid possible postcodes. If they were rapidly changing, maybe the latter,
but as they don't, I think the former.

BTW, looked at your website. If you ever need any Excel/VBA consultancy
.... :-)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Leslie Isaacs said:
Bob

I honestly don't know whether, say, AC will ever get used, but as the
'rules' allow it I think it would be safest to allow it in my data. The
risk of someone complaining that I didn't allow their (AC...) postcode is
greater than the risk of my allowing a valid but unassigned postcode. I
am curious though where you found the list of assigned postcodes?

Les
Click to expand...
Click to expand...
 
Data Validity userdefined T1: =VLOOKUP(S1*10+T1,Z1:Z13,1,)

Z1:Z13: 11, 12, 13, 22, ..., 46
[ if T1 can be <=99: 101, 102, ... with =VLOOKUP(S1*100+T1,Z1:Z13,1,) ]

Ignore the error when building Data Validity (xl2000)
 
Then you are still looking right, as Rick's code only validates current
postcodes?

As for Access, Excel, the VBA is the same, but clearly a different object
model. If your app is Access, why do you post your questions to the Excel
groups?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Leslie Isaacs said:
Bob

Thanks for these references.

As mentioned earlier I'm going to stick with validating possible postcodes
as opposed to current postcodes as the tighter restriction is more complex
(!) and more likely to lead to a false rejection - compared to the risk of
the looser restriction leading to a false acceptance.

On the consultancy front, I'll certainly bear your offer in mind if and
when the need arises. The programme we use is an MS Access application
(Access97 in fact!), but I assume that Access's VBA is the same as
Excel's?

Les


Bob Phillips said:
Les,

I found it here http://www.evoxfacilities.co.uk/evoxar.html

and here http://www.evoxfacilities.co.uk/pcdarea.htm

If AC did ever get used, I think it would be easy to change Rick's code
to accommodate. The part

Parts(0) Like "A[0-9BL]#*"

changes to

Parts(0) Like "A[0-9BCL]#*"

and you have to ask whether you validate for valid current postcodes or
valid possible postcodes. If they were rapidly changing, maybe the
latter, but as they don't, I think the former.

BTW, looked at your website. If you ever need any Excel/VBA consultancy
.... :-)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Leslie Isaacs said:
Bob

I honestly don't know whether, say, AC will ever get used, but as the
'rules' allow it I think it would be safest to allow it in my data. The
risk of someone complaining that I didn't allow their (AC...) postcode
is greater than the risk of my allowing a valid but unassigned postcode.
I am curious though where you found the list of assigned postcodes?

Les


Well, it is a bit simpler Les!

BTW I bowed out of that thread because Rick cam back and has spent more
time on it in the past and understood the rules better (I just spotted
a logic flaw in his original code), and I don't want to confuse that
thread more, but I did look up current postcodes and see that only AB
and AL are valid. Do you really think that they may they might add say
AC? I had always thought that the regions (or whatever they call them)
were fixed, and any extra housing/towns would get a sub id within the
main region.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Bob

I hardly dared ask you or Rick to look at this one, hence the new
thread: but many thanks for the answer.
And what's more, unlike the postcode validation code, I understand it
(and so can now adapt it to suit my more general requirement)!

Thanks again
Les




=OR(AND(S1=1,T1>=1,T1<=3),AND(S1=2,T1>=2,T1<=5),AND(S1=3,T1>=3,T1<=5),AND(S1=4,T1>=3,T1<=6,T1<>4))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Oops - that should have been "I need to validate data being entered
in COLUMN T ..."
Les

Hello All

I need to validate data being entered in row T according to the
entry in the same row in column S, i.e.

if S1=1 then T1 must be 1, 2 or 3
if S1=2 then T1 must be 2, 3, 4 or 5
if S1=3 then T1 must be 3, 4 or 5
if S1=4 then T1 must be 3, 5 or 6
etc.

There is no mathematical relationship betwen the S1 value and the
valid T1 values.
S1 must be between 1 and 15, so validation of S1 is simple, but I
have tried to get the conditional validation of T1 but just can't
get the syntax right!

Hope soneone can help.

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
Bob

This particular question related to my using an excel sheet for clients to
enter their staff details.
If you look at the access newgroups you will see that I am no stranger to
them!! (although in fact I don't think I've used then in the last few
months).

Thanks
Les


Bob Phillips said:
Then you are still looking right, as Rick's code only validates current
postcodes?

As for Access, Excel, the VBA is the same, but clearly a different object
model. If your app is Access, why do you post your questions to the Excel
groups?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Leslie Isaacs said:
Bob

Thanks for these references.

As mentioned earlier I'm going to stick with validating possible
postcodes as opposed to current postcodes as the tighter restriction is
more complex (!) and more likely to lead to a false rejection - compared
to the risk of the looser restriction leading to a false acceptance.

On the consultancy front, I'll certainly bear your offer in mind if and
when the need arises. The programme we use is an MS Access application
(Access97 in fact!), but I assume that Access's VBA is the same as
Excel's?

Les


Bob Phillips said:
Les,

I found it here http://www.evoxfacilities.co.uk/evoxar.html

and here http://www.evoxfacilities.co.uk/pcdarea.htm

If AC did ever get used, I think it would be easy to change Rick's code
to accommodate. The part

Parts(0) Like "A[0-9BL]#*"

changes to

Parts(0) Like "A[0-9BCL]#*"

and you have to ask whether you validate for valid current postcodes or
valid possible postcodes. If they were rapidly changing, maybe the
latter, but as they don't, I think the former.

BTW, looked at your website. If you ever need any Excel/VBA consultancy
.... :-)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Bob

I honestly don't know whether, say, AC will ever get used, but as the
'rules' allow it I think it would be safest to allow it in my data. The
risk of someone complaining that I didn't allow their (AC...) postcode
is greater than the risk of my allowing a valid but unassigned
postcode. I am curious though where you found the list of assigned
postcodes?

Les


Well, it is a bit simpler Les!

BTW I bowed out of that thread because Rick cam back and has spent
more time on it in the past and understood the rules better (I just
spotted a logic flaw in his original code), and I don't want to
confuse that thread more, but I did look up current postcodes and see
that only AB and AL are valid. Do you really think that they may they
might add say AC? I had always thought that the regions (or whatever
they call them) were fixed, and any extra housing/towns would get a
sub id within the main region.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Bob

I hardly dared ask you or Rick to look at this one, hence the new
thread: but many thanks for the answer.
And what's more, unlike the postcode validation code, I understand it
(and so can now adapt it to suit my more general requirement)!

Thanks again
Les




=OR(AND(S1=1,T1>=1,T1<=3),AND(S1=2,T1>=2,T1<=5),AND(S1=3,T1>=3,T1<=5),AND(S1=4,T1>=3,T1<=6,T1<>4))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)

Oops - that should have been "I need to validate data being entered
in COLUMN T ..."
Les

Hello All

I need to validate data being entered in row T according to the
entry in the same row in column S, i.e.

if S1=1 then T1 must be 1, 2 or 3
if S1=2 then T1 must be 2, 3, 4 or 5
if S1=3 then T1 must be 3, 4 or 5
if S1=4 then T1 must be 3, 5 or 6
etc.

There is no mathematical relationship betwen the S1 value and the
valid T1 values.
S1 must be between 1 and 15, so validation of S1 is simple, but I
have tried to get the conditional validation of T1 but just can't
get the syntax right!

Hope soneone can help.

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
I have looked at your code and can't see why you do all those Parts(0)
checks up-front when you then check them all again later.

Isn't this version (BTW I have changed the multiple IF test to Select Case
to avoid the lack of short-circuiting) working just as well, or am I
missing something?
Click to expand...

Originally, I didn't check them a second time; rather, the first check
attempted to make sure the "shape" of the text corresponded to the many
general rules of the UK postal system with the remainder checking the
longish list of specific areas if the text met the general shape
requirements; however, as the progress of the other thread showed, my
original "shape" checking was not accurate. The "final" version (which is
what you have adapted below) was eventually arrived at after several
iterations of corrections in response to Leslie's pointing out those places
it failed to work and, as it turned out, the eventual patching created a lot
of duplicate checks. Your revision is fine although I would have used a
nested If-Then structure to control the "short-circuiting" (my programming
experience dates back to before Select Case structuring was an option; and
so, as a rule, my thinking processes tend toward If-Then constructions).

Rick


Function ValidPostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String

PostCode = UCase$(PostCode)
Parts = Split(PostCode, " ")

'check the two abnormal possibilities
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Then

ValidPostCode = True
'check second part doesn't use Q, V or X in first letter
ElseIf Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" Then

'depending upon first letter, validate first part
Select Case Left$(Parts(0), 1)

Case "A": ValidPostCode = Parts(0) Like "A[0-9BL]#*"
Case "B": ValidPostCode = Parts(0) Like "B[0-9ABDHLNRST]#*"
Case "C": ValidPostCode = Parts(0) Like "C[0-9ABFHMORTVW]#*"
Case "D": ValidPostCode = Parts(0) Like "D[0-9ADEGHLNTY]#*"
Case "E": ValidPostCode = Parts(0) Like "E[0-9CHNX]#*"
Case "F": ValidPostCode = Parts(0) Like "F[0-9KY]#*"
Case "G": ValidPostCode = Parts(0) Like "G[0-9LU]#*"
Case "H": ValidPostCode = Parts(0) Like "H[0-9ADGPRSUX]#*"
Case "I": ValidPostCode = Parts(0) Like "I[0-9GPV]#*"
Case "J": ValidPostCode = Parts(0) Like "K[0-9ATWY]#*"
Case "K": ValidPostCode = Parts(0) Like "J##"
Case "L": ValidPostCode = Parts(0) Like "L[0-9ADELNSU]#*"
Case "M": ValidPostCode = Parts(0) Like "M[0-9EKL]#*"
Case "N": ValidPostCode = Parts(0) Like "N[0-9EGNPRW]#*"
Case "O": ValidPostCode = Parts(0) Like "O[0-9LX]#*"
Case "P": ValidPostCode = Parts(0) Like "P[0-9AEHLOR]#*"
Case "R": ValidPostCode = Parts(0) Like "R[0-9GHM]#*"
Case "R": ValidPostCode = Parts(0) Like
"S[0-9AEGKLMNOPRSTWY]#*"
Case "T": ValidPostCode = Parts(0) Like "T[0-9ADFNQRSW]#*"
Case "U": ValidPostCode = Parts(0) Like "U[0-9B]#*"
Case "W": ValidPostCode = Parts(0) Like "W[0-9ACDFNRSV]#*"
Case "Y": ValidPostCode = Parts(0) Like "Y[0-9O]#*"
Case "Z": ValidPostCode = Parts(0) Like "Z[0-9E]#*"
End Select
End If
End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Rick Rothstein (MVP - VB) said:
I found them here....

http://www.answers.com/topic/list-of-postcode-areas-in-the-united-kingdom

By the way, I posted a revised function for you to look at back in the
other thread.

Rick
Click to expand...
Click to expand...
 
Back
Top