More validadtion problems!

[Les Isaacs]

Hello All

I have a worksheet that will be used for simple data entry of name, address,
etc etc. - 1 column per field. One of the columns is for National Insurance
number, and one is for postcode (UK), and I am struggling to get the
validation for either of these.

National insurance number:
This must be in the format AA000000A (i.e. two letters followed by 6
numerics followed by one letter) - BUT only certain combinations of the
first two letters are valid (e.g. WD is OK but WR is not), and only certain
letters at the end are valid: I have a list of the valid combinations of
first two letters (there are 48 of them!) and the valid last letters (there
are 7).

Postcode:
There are 6 valid formats:
AN NAA - e.g. M1 1AA
ANN NAA - e.g. M60 1NW
AAN NAA - e.g. CR2 6XH
AANN NAA - e.g. DN55 1PT
ANA NAA - e.g. W1A 1HQ
AANA NAA - e.g. EC1A 1BB
But -
The letters Q, V and X are not used in the first position.
The letters I, J and Z are not used in the second position.
The only letters to appear in the third position are A, B, C, D, E, F, G, H,
J, K, S, T, U and W.
The only letters to appear in the fourth position are A, B, E, H, M, N, P,
R, V, W, X and Y.
The second half of the Postcode is always consistent numeric, alpha, alpha
format and the letters C, I, K, M, O and V are never used.

Hope someone can help.
Many thanks
Les

 

[Bob Phillips]

NI Number

=AND(ISNUMBER(MATCH(LEFT(C2,2),valid_first,0)),ISNUMBER(MATCH(RIGHT(C2,1),valid_last,0)),ISNUMBER(--MID(C2,3,5)))

Postcode

This works as conditional; formatting, but doesn't seem to work as DV. It is
not pretty, haven't tested all combinations, but seems to comply to your
rules

Create the following name/referso pairs

char1 ={"Q","V","X"}
char2 ={"I","J","Z"}
char3 ={"
","A","B","C","D","E","F","G","H","J","K","S","T","U","0","1","2","3","4","5","6","7","8","9"}
char4 ={"
","A","B","E","H","M","N","P","R","V","W","X","Y","U","0","1","2","3","4","5","6","7","8","9"}
charend ={"C","I","K","M","O"}
validFirstTwo
=AND(NOT(ISNUMBER(MATCH(LEFT(Sheet1!$C2,1),char1,0))),NOT(ISNUMBER(MATCH(MID(Sheet1!$C2,2,1),char2,0))))
validMiddleFew
=AND(ISNUMBER(MATCH(MID(Sheet1!$C2,3,1),char3,0)),ISNUMBER(MATCH(MID(Sheet1!$C2,4,1),char4,0)),ISNUMBER(--MID(Sheet1!$C2,FIND("
",Sheet1!$C2)+1,1)))
validLastTwo
=AND(NOT(ISNUMBER(MATCH(MID(Sheet1!$C2,LEN(Sheet1!$C2)-1,1),charend,0))),NOT(ISNUMBER(MATCH(RIGHT(Sheet1!$C2,1),charend,0))))
validPostCode =AND(ValidFirstTwo,validMiddleFew,validLastTwo)

and then use this formula in CF

=NOT(validPostCode)

=AND(NOT(ISNUMBER(MATCH(LEFT(C13,1),char1,0))),NOT(ISNUMBER(MATCH(MID(C13,2,1),char2,0))),ISNUMBER(MATCH(MID(C13,3,1),char3,0)),ISNUMBER(MATCH(MID(C13,4,1),char4,0)),ISNUMBER(--MID(C13,FIND("
",C13)+1,1)),NOT(ISNUMBER(MATCH(MID(C13,LEN(C13)-1,1),charend,0))),NOT(ISNUMBER(MATCH(RIGHT(C13,1),charend,0))))

--
HTH

Bob

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

 

[excel-ant]

Hi Les,

Looks like the National Insurance Number valdation is easier to set
up.

Go to Data > Validation. Set Allow = Custom.
You need to write a comprehensive formula into the Formula field to
validate your entries.

You will need to employ the following functions. LEFT(), RIGHT(),
MID(), ISNUMBER(), ISTEXT() etc.
You can use AND(test1,test2,...,test n) to add several tests where all
must be tre.
Or OR(test 1, test 2, ....., test n) where one of must be true.

TEST 1. So NINO should be a 9 alphanumeric string
so the following should be true

=len(A2)=9

TEST 2. positions 3-8 should be numeric

=isnumber(value(mid(A2,3,6)))

TEST 3. For example WD is one of 48 valid combinations for the first
two positions, but also CF is valid also. Use OR

=OR(left(A2,2)="WD",left(A2,2)="CF")...... you need to add all 48
combinations

To test all of these, stack them up in an AND formula. For example.

=AND(LEN(A2)=9,ISNUMBER(VALUE(MID(A2,3,6))),OR(LEFT(A2,2)="WD",LEFT(A2,2)="CF"))

Hope this helps,

It is obviously very fiddly but the principles are quite
straightforward,

I would use these functions in the worksheet first by adding each test
to a different cell and then stack them up when you know they all
work.
Let me know if you need anything further,

Ant
http://www.excel-ant.co.uk

 

[Les Isaacs]

Bob

I have got the NI number working as per your suggestion - many thanks for
that.

Regarding the postcode validation, I am struggling to know exactly how to
follow your instructions.

I already have lists of valids (on another sheet - sheet2) for the NI number
(valid_first and valid_last): where you say "Create the following
name/referso pairs" do you mean I should create five more lists, called
char1 (with members Q, V and X), char2 (with members I, J and Z), char3
(with the longer list of members as below), char4 (ditto) and charend (with
members C, I, K, M and O)?

Then, I am not sure what to do with the three expressions you have given for
ValidFirstTwo, validMiddleFew and validLastTwo. Are these also entered as
'lists'? Also, where these three expressions contain a reference to sheet1,
is this assuming that the main data input sheet is sheet1?

With the above in place, I understand (I think!) that I then highlight the
entite column that is to hold the postcode data, and do Format>Conditional
formatting, change the first combo to Formula is, then enter
=NOT(validPostCode) in the adjacent formula bar: is that correct? If so, why
is the NOT part there - surely I want the validPostCode expressions to be
satisfied, as opposed to NOT being satisfied.

Finally, with all the above in place, what do I do with the final expression
that you have given - i.e.
=AND(NOT(ISNUMBER(MATCH(LEFT(C13,1),char1,0))),NOT(ISNUMBER(MATCH(MID(C13,2,1),char2,0))),ISNUMBER(MATCH(MID(C13,3,1),char3,0)),ISNUMBER(MATCH(MID(C13,4,1),char4,0)),ISNUMBER(--MID(C13,FIND("

",C13)+1,1)),NOT(ISNUMBER(MATCH(MID(C13,LEN(C13)-1,1),charend,0))),NOT(ISNUMBER(MATCH(RIGHT(C13,1),charend,0))))

Sorry if I seem to be asking too many questions, but I know I'm almost
there - and I am in fact leraning a lot!!

Many thanks once again for your continued help.
Les

 

[Rick Rothstein \(MVP - VB\)]

This formula should correctly handle the National Insurance Number...

=AND(NOT(ISERROR(SEARCH(LEFT(A1),"ABCEGHJKLMNOPRSTWXYZ"))),NOT(ISERROR(SEARCH(MID(A1,2,1),"ABCEGHJKLMNPRSTWXYZ"))),SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"0123456789")))=6,IF(LEN(A1)=8,TRUE,IF(LEN(A1)=9,NOT(ISERROR(SEARCH(RIGHT(A1),"ABCD
"))))),ISERROR(SEARCH(LEFT(A1,2),"BGB*NKN*TNT*ZZ")))

As for the UK Postcodes... I once posted the macro function below my
signature to handle that. If you can make use of a macro function in your
spreadsheet, then put the function below in a Module so that you can use it
within your spreadsheet just like a built in function. If you are not sure
how to do that.... right click the worksheet's tab and select View Code from
the popup menu that appears; once inside the VBA editor, click on
Insert/Module from its menu bar and simply Copy/Paste the code below into
the Module's code window; that's it, simply use =Validate(A1) in any cell on
the spreadsheet to check the postcode in A1.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
End Function

 

[Bob Phillips]

Les,

I am referring to Excel defined names (Insert>Name>Define...), and put the
name in the first box, the Refersto value in the bottom box.

That last expression should be ignored. That was me trying to do it in one
step, and I forgot to remove it.

Is that clear now?

--
HTH

Bob

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

 

[Rick Rothstein \(MVP - VB\)]

This formula should correctly handle the National Insurance Number...

=AND(NOT(ISERROR(SEARCH(LEFT(A1),"ABCEGHJKLMNOPRSTWXYZ"))),NOT(ISERROR(SEARCH(MID(A1,2,1),"ABCEGHJKLMNPRSTWXYZ"))),SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),"0123456789")))=6,IF(LEN(A1)=8,TRUE,IF(LEN(A1)=9,NOT(ISERROR(SEARCH(RIGHT(A1),"ABCD
"))))),ISERROR(SEARCH(LEFT(A1,2),"BGB*NKN*TNT*ZZ")))

Using the number test part that 'excel-ant' posted (it's much better than
the one I used), we can shorten the above formula a little bit...

=AND(NOT(ISERROR(SEARCH(LEFT(A1),"ABCEGHJKLMNOPRSTWXYZ"))),NOT(ISERROR(SEARCH(MID(A1,2,1),"ABCEGHJKLMNPRSTWXYZ"))),ISNUMBER(VALUE(MID(A1,3,6))),IF(LEN(A1)=8,TRUE,IF(LEN(A1)=9,NOT(ISERROR(SEARCH(RIGHT(A1),"ABCD
"))))),ISERROR(SEARCH(LEFT(A1,2),"BGB*NKN*TNT*ZZ")))

Rick

 

[Rick Rothstein \(MVP - VB\)]

I just noticed in both my postings, my newsreader picked an "unfortunate"
spot to wrap the formula at, so I am thinking that could have happened to
you also. In both cases, there is a space character following the ABCD (and
immediately before the closing quote mark) toward the end of the formula.
Just thought I would mention that in case there was any confusion as to the
intent at that location.

Rick

 

[Les Isaacs]

Bob

OK - I think I'm getting there: can you just confirm that the 3 expressions
for ValidFirstTwo, validMiddleFew and validLastTwo should also be entered as
excel defined names (in the same way as char1 etc.)?

Thanks
Les

 

[Les Isaacs]

Rick

Many thanks for this.

I'm sure I'm being thick, but I can't get your postcode validation module to
work.
I have pasted the code into a new module, but when I tried to set the
validation for the first cell in the postcode column (columnI) I did
Data>Validation, selected Custom in the 'Allow' combobox, then entered
=Validate(I2) in the 'Formula' box, but I then got the message "A named
range you specified could not be found". What have I done wrong? Ultimately
I need the validation to be carried out on every row of columnI - at the
time the postcode is entered. Should I be able to use the code you provided
in this way?

Thanks for your continued help.
Les

 

[Bob Phillips]

That's an easy question. Yes

--
HTH

Bob

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

 

[Rick Rothstein \(MVP - VB\)]

I can't get it to work directly in Data Validation either. I'm guessing a
UDF can't be used that way. The function does work directly in a spreadsheet
cell though, so you can implement your Data Validation in this indirect way.
Find an unused column (for purposes of this example, I'll assume that column
is Z) and put this formula in its 2nd row (Z2)...

=ValidatePostCode(I2)

where I am assuming your PostCode column is Column I (as mentioned in your
message) and copy it down as far as necessary. Now, so your users don't see
it, select the entire Column Z and Hide it. Now, select all of Column I and
click on Date/Validation in Excel's menu bar. On the Settings tab in the
dialog box that appears, select "Custom" from the "Allow" drop-down and put
=I1 in the "Formula" field. Set whatever you need to on the other Tabs and
then click OK.

Just one more thing... in the code on the Module, change the function's
procedure declaration statement to this...

Function ValidatePostCode(ByVal PostCode As String) As Boolean

All I did is add "As Boolean" to the statement you now have there.

Okay, your Data Validation should now work.

Rick

 

[Les Isaacs]

Rick

I don't know what I'm doing wrong, but it's not working!

I think it is something to do with some other code that I have on this
sheet, because when I followed your instructions below, on entering
=ValidatePostCode(I2)
in cell AW2 (column AW is the first unused column), I get the message "You
did not enter a valid time", and the cells displays #VALUE!.
This message is in fact generated by the other code (which I have pasted
below), but what I don't understand is that the other code should only
affect the range U2:AV99 - i.e. it should not affect columns AW and after.

The other thing I don't understand is when you say
"select all of Column I and click on Date/Validation in Excel's menu bar. On
the Settings tab in the dialog box that appears, select "Custom" from the
"Allow" drop-down and put =I1 in the "Formula" field."
Is this right? To my mind this validation is saying that the value of I2
must be the value of I2, and the value of I3 must be the value of I3, etc.
etc. I wonder if I should be putting =AW1 in the "Formula" field? But first
of all I have to sort out the formula
=ValidatePostCode(I2)
in AW2, etc
In case it's relevant, each value in row1 is a column heading - so any
validations etc should only be applied to row2 and after.

I hope this makes sense to you and that you can see where I'm going wrong.

Many thanks once again
Les
NB
The other code on this sheet is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "U2:AV99"
Dim TimeStr As String

On Error GoTo EndMacro
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Application.Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & Mid(.Value, 2, 2) & _
":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & Mid(.Value, 3, 2) & _
":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value <> ""
And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(2).Column).Value <> "" And
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value > Me.Cells(.Row,
Me.Range(WS_RANGE).Columns(2).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If
If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(3).Column).Value <> ""
And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(4).Column).Value <> "" And
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(3).Column).Value > Me.Cells(.Row,
Me.Range(WS_RANGE).Columns(4).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(5).Column).Value <> ""
And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(6).Column).Value <> "" And
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(5).Column).Value > Me.Cells(.Row,
Me.Range(WS_RANGE).Columns(6).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

 

[Les Isaacs]

Bob

I have done everything as per your instructions, but I simply can't get it
to work! Whatever I type in the postcode column is accepted.

I realise it may be impossible for you to guess why it is not working here
if it works at your end, but if you do have any ideas I can try I would be
very grateful (I'm very grateful anyway!).

Hope we can find the answer.
Thanks again
Les

 

[Bob Phillips]

Les,

I have posted an example here http://cjoint.com/?kvwD1o1nfy

--
HTH

Bob

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

 

[Les Isaacs]

Bob

I can see the example in an Internet Explorer window, but I can't see them
'in excel' so I can't see the defined names. I'm not sure what I should be
able to see in the example that I can check against what I have.

Sorry to be a pain, but I have gone over your instruction a number of times
and just can't see what I've done wrong!

Could I email you the workbook I have?

Thanks once again.
Les

 

[Peo Sjoblom]

Why don't you download his example then open it in Excel?


--

Regards,

Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

Sorry about the delay in getting back to you.... just recovered from a major
system crash. Still testing things out, but I wanted to respond to the two
points you raised. See inline comments.

I don't know what I'm doing wrong, but it's not working!

I think it is something to do with some other code that I have on this
sheet, because when I followed your instructions below, on entering
=ValidatePostCode(I2)
in cell AW2 (column AW is the first unused column), I get the message "You
did not enter a valid time", and the cells displays #VALUE!.
This message is in fact generated by the other code (which I have pasted
below), but what I don't understand is that the other code should only
affect the range U2:AV99 - i.e. it should not affect columns AW and after.

I'll get back to you on this one when time permits (hopefully, later on
today or this evening). For some reason, you are getting your Worksheet
Change event's error message. I don't see anything obvious, so I will need
to set your code up and actually try it out to see if I can duplicate the
problem. If you start a new spreadsheet and only put in the code I gave you
in my previous postings and follow the directions I gave you previously
(making the one correction I note below), you should be able to see the code
in action.

The other thing I don't understand is when you say
"select all of Column I and click on Date/Validation in Excel's menu bar.
On the Settings tab in the dialog box that appears, select "Custom" from
the "Allow" drop-down and put =I1 in the "Formula" field."
Is this right? To my mind this validation is saying that the value of I2
must be the value of I2, and the value of I3 must be the value of I3, etc.
etc. I wonder if I should be putting =AW1 in the "Formula" field? But
first of all I have to sort out the formula
=ValidatePostCode(I2)
in AW2, etc

Instead of saying =I1, it should have said =Z1 (using my example column)...
given the information above, that formula should be =AW1 as you noted.


Rick

 

[Bob Phillips]

Of course you can Les.

--
HTH

Bob

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

 

[Leslie Isaacs]

Rick

Many thanks for your further reply.

I have done as you suggested and started with a new workbook, and ... it
works!
Having said that, I may need to make a minor alteration to your code,
because the Post Office have altered the rules since you wrote it! (or
perhaps your code is in fact more up to date than the rules at
http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. - I note that these
rules are dated Sept 2002).

According to these rules the letters M, N and some others should not be
allowed in the third position, but I think your code allows any letter in
that position. I have had a look at your code hoping to be able to 'spot'
how to update if if necessary, but it was beyond me! If you could guide me
in the right direction I would be very grateful (although I know I need
first to find a more up-to-date ruleset!).

I also still can't work out why my 'other' code is interfering with yours in
my original workbook: have you had any luck with that?

I really am extremely grateful for your help with this.

Les