• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation - NI Number field

J

Jaine Wills

New Member
Hi All,

This is probably such an easy question but I can't seem to make it work. I am trying to put some data validation into an NI number cell (UK) so that the chances of error by the admin staff are minimised.

Any ideas?

Thanks
Jaine
 
Hi Janie,

Welcome to the forum..
Can you please elaborate little more..

* are you looking for
* Zip Code validation for UK post code.. (AA9 9AA)
* At the same time you want validation for NI (Northern Ireland Post Code ([BT]9 9AA)

and that only by Excel Data validation..

In Web, lot of post for GB/CANADA post code validation.. just google it..
 
Hi Debraj

Thanks - I am looking to only allow a certain type of entry. For example, JJ112233P. I want to make it easy for the admin team so that if they enter J1112233P they will be informed that the format is incorrect. I tried =LLNNNNNNL but that didn't seem to work :(

Thanks
Jaine
 
Good day Jaine and welcome to the forum.

NI numbers are sensitive data and security in Excel at best is weak at worst non-existent.

Do you have Access where you work, it would be easy to but an input mask on the NI field in an Access table to cover the NI format and issue warnings on wrong data input.
By splitting the database in to back-end and front-end you would stop anyone being able to get at the raw data tables and screw it up, your admin staff would have a form as a front-end and would only be able to work on one record at a time with no access to the data tables.

You could lock data cells in the form so that when records are being viewed form cells such as name/date of birth/NI number/sex could not be edited as it is highly unlikely this type of data would be changed.


.
 
Good day =DEC2HEX(3563):p

The NI that Janie is referring to is the National Insurance number that all citizens in the UKGB have, it is an alpha-numerical code in the format of Alpha-Alpha(space)Numeric-Numeric(space)Numeric-Numeric(space)Numeric-Numeric(space)Alpha. Nine pieces in total, AA 11 22 33 44 A.

Each NI number is unique to the person.
 
Thanks Bobhc :) However, my admin staff could not cope with entering things in different tables. They need to be able to enter the NI number as a solid field - with the validation present to prevent them messing it up.
 
But janie that's the whole point of Access, as I said your admin staff do not at any time have access to the data tables just a front-end form.

I am assuming that they would be entering other data relating to a person such as name/address/date of birth/sex/date of joining ect ect as well as the NI number

They would not be concerned with how to enter data as this would all be set up in the form and they would as I pointed out be restricted in what they could enter and what the could not edit.
 
You are assuming our admin staff are logical beings lol

Access is something which we are moving to but at the moment an excel sheet is all we have. They will have to hard enter the NI code and I just want to make sure that, whilst it could still be wrong, the format is right. Can I set a DV code to allow LLNNNNNNL?
 
You are assuming our admin staff are logical beings lol
Click to expand...

Most definitely not:D

I have worked with many admin types and I soon learned that if you set something up for them they will immediately rebel against it, that's why I used Access to stop them in their tracks. Set up a form that dictates what they can enter and how they can enter and that really gets their backs up as you remove their ability to rebel.....:p

Golden rule that worked was to tell them, use it or move on.
 
Ha ha my admin staff are of limited ability as I work for a charity which employs disabled people. At the moment Excel is all they can handle, hence the need to constrict their input. I understand what you are saying with regards to Access but can it be done in Excel in the meantime?
 
I think that the only way you can do this is with a UDF in VBA would that be OK with your employees.
 
Possibly, but can I run this on a live basis, without them having to do anything? Surely there's an easier way though? Excel is logical, yes? So there must be a way of saying accept a Letter, Letter, Number (*6), Letter?? I know I am very black v white but then that's Excel too...?
 
Hi Jaine,
Depending on the capabilities of your users, one could create more or less elaborate validation schemes. But you could try the following for a value in A1:
=IFERROR(SUMPRODUCT(N(N(ISERR(-MID(SUBSTITUTE($A1," ",""),ROW(OFFSET(A$1,,,LEN(SUBSTITUTE($A1," ","")))),1)))={1;1;0;0;0;0;0;0;0;0;1}))=11,FALSE)

It should return TRUE if the NI number passes validation; FALSE otherwise. It should also handle any spaces in the input.

(Please note that the formula is not idiot proof. One can defeat it easily be supplying something other than a number where an Alpha char is expected. (For example, a # sign.) We can make this more sophisticated if the situation calls for it.)

You can then make the formula as part of a helper cell, conditional formatting rule, etc.

Cheers,
Sajan.
 
Hi Sajan - I tried this within a DV rule but I got an error message which, tbh, I did not fully understand. The long and the short of it is it won't work. That way! My main aim is not so much to highlight the entries that are incorrect but rather to ensure that the error message pops up as the user is inputting. Once it's input and saved (incorrectly) it is too late.

Hi Narayan - thanks for the links but sadly, they mean very little to me as I do not really understand them. I used to write macros years ago but, as the adage says, use it or lose it and I'm afraid I've now lost it!

I need the validation at the point of input. I'm beginning to think that it isn't possible.... :(
 
Hi Jaine ,

The macros given in the links will ensure that the validation will be done on the input , and invalid data entry will be prevented.

I think if you used to write macros earlier , you should be able to get back your familiarity and fluency with a little effort ; rather than look for ready-made solutions in forums , use them to learn , so that in the long-term you become a contributor !

Narayan
 
Hi Janie,

try this.. as a DV

=(LEN($C3)=9) * ISNUMBER(SEARCH(LEFT($C3), "ABCEGHJKLMNOPRSTWXYZ")) * ISNUMBER(SEARCH(MID($C3,2,1), "ABCEGHJKLMNPRSTWXYZ")) * ISNUMBER(--MID(A3, 3, 6)) * ISNUMBER(SEARCH(LEFT($C3), "ABCD "))

Check the attached for more detail..
 

Attachments

  • Chandoo#12880.xlsx
    9.1 KB · Views: 5
Hi Narayan

Thank you; I intend to but time is, unfortunately, something I am really short of at the moment due to having to have this done pdq for a funding application. I am intending to rebuild my knowledge and I am not afraid of a little hard work - it's just a timing thing at the moment.

Hi Debra

Thanks for that however, when I tried to enter an incorrect NI number I did not get an error message (on my sheet) and then, when I just copied what you already had on the sheet you attached (on your sheet) I got an error message.

Thanks :)
 
Hi Jaine,
Data Validation in Excel requires specific rules to be followed. (For example, dynamic arrays are not supported directly in the DV functionality.) That is why I suggested use of a helper cell above.

Please note that a formula based DV can always be defeated easily by a user. If your users are Excel challenged, you may be better off using macros, or better yet, another application altogether. If those options are not possible, then you may want to point out the limitations of the tool to your users.

Cheers,
Sajan.
 
Hi, Jaine Wills!

Assuming that the validation rules are correctly stated here:
http://www.mrexcel.com/forum/excel-...date-uk-national-insurance-numbers-ninos.html
and that Debraj(ex-Roy)'s formula matches them, then give a look at the uploaded file. It uses data validation for column C entries, pops up a display with the rules that NI must comply, and displays a message error if not valid.

Does this help?

Regards!

PS: Not tested, just a solution built the previous quoted info.
 

Attachments

  • Data Validation - NI Number field (for Jaine Wills at chandoo.org).xlsx
    9.2 KB · Views: 9
Hi Sajan and SirJB7

Many thanks for trying to help me on this. It is not working though and, to be honest, these forums have only caused me pain in that I "thought" I was good at Excel but it would appear that I know nothing: which is rather demoralising. Sadly, I do not know what a helper cell is or what a dynamic array is and so I am going to struggle making this sheet accessible for the users as, once done, the team will have to maintain it.

So...I am bowing out, not giving up, just leaving it alone now. Thanks for all your help (everyone) but I suspect I need to do some training!
 
Hi, Jaine Wills!
Once you end the therapy that you should be starting right now and your psychologist say "Please, don't come back any more!" (BTW, have you seen What About Bob?, with Bill Murray and Richard Dreyfuss... don't become a female Bob!), you should consider uploading a sample file (don't include any actual NI since they're sensitive and private information), it'd be very useful for those who read this and might be able to help you, who surely could update your model to fit the data validation formulas required.
Regards!
 
Ha ha yes, I will be going in for therapy at this rate! Haven't seen the film so not sure whether I should be insulted, chuffed or plain scared :D. I have uploaded a sample on another thread but, to save you searching, I have attached it here as well.

Btw, Access! Mmmmm, that is something that we are moving to as it will hold everything we need and the output can be tailored to what is needed on a much bigger scale than what I need now. However, this is a WIP at the moment with our IT guy (when he gets around to it) putting it all together. So, we'll leave that well alone but, yes, you are right Bob; that is, apparently, the answer. Eventually.

Anyway, fill your boots - be awesome! (and thanks again) :)
 

Attachments

  • Sample spreadsheet.xlsx
    18.1 KB · Views: 7
You must log in or register to reply here.
Back
Top