restricting input of certain format (National Insurance numbers)

[cwilliams96]

I would like to be able to if a user enters into a particular cell any other format but text,text,num,num,num,num,num,num,text it wont allow it...

Basically users are entering national insurance numbers and although this wont restrict actual incorrect numbers being entered, at least it would force the correct format....

I have looked at custom formats but dont think this is correct.. I currently have data validation on checking for duplicates also.

Not sure how to do this one???

Thanks

Chris

 

[dmt32]

Not my strongest point, but if using data validation, then try the custom selection in the data validation dialog and this example formula to validate cell A1 (change as required)</SPAN>

=AND(LEN(A1)=9,ISERROR(MID(A1,1,1)*1),ISERROR(MID(A1,2,1)*1),ISNUMBER(MID(A1,3,6)*1),ISERROR(MID(A1,9,1)*1))


You can add error message like "Not Valid NI Number" if required.


Dave</SPAN>

 

[cwilliams96]

Thanks. But how do you have 2 data validations on? In this case one checking for duplicates and one for correct input?

 

[dmt32]

Thanks. But how do you have 2 data validations on? In this case one checking for duplicates and one for correct input?

Hi,</SPAN>
As I said, Data Validation is not one of my strongest points as seldom have need to use it. Data validation formulas must resolve to a value of TRUE or FALSE, so that the validation knows whether or not to reject an entry so in theory, to test for two entry errors, all you need to do is combine the two formulas - something like this as an example:</SPAN>

I have assumed NI numbers are in Column A.</SPAN>

=AND(AND(LEN(A2)=9,ISERROR(MID(A2,1,1)*1),ISERROR(MID(A2,2,1)*1),ISNUMBER(MID(A2,3,6)*1),ISERROR(MID(A2,9,1)*1)),COUNTIF($A$1:$A$100,A1)=1)</SPAN>

It’s not very elegant & perhaps someone else with better understanding may be able to offer a cleaner solution.</SPAN>
But for now, this may (or may not) go in right direction for you but you will need to experiment.</SPAN>

You can then add the error message “Invalid NI Number Or Duplicate Entry”</SPAN>

You could of course use VBA but this has its own drawbacks, mainly if user does not enable macros!</SPAN>

Hope of some help.</SPAN>

Dave.</SPAN>

 

[cwilliams96]

Thats great thanks..... I'll give it a go

 

[Comfy]

You could of course use VBA but this has its own drawbacks, mainly if user does not enable macros!

If you were worried about users not having macros enabled you could do what I do and have a "Your macros are disabled" sheet.
This sheet will be visible and the National Insurance number sheet would be hidden. If Macros are enabled you have code that would hide the notification sheet and display the National Insurance number sheet.

With regards to the validation of the number you could use regular expressions, something like this: UK National Insurance Number Validation - VBA Express Forum or this: http://www.mrexcel.com/forum/excel-...date-uk-national-insurance-numbers-ninos.html

If you would like some help with it, then let me know. I could probably send you the worksheet that I use to notify the user about macros. The screenshots it contains are from xl2003 but you could update those easily if you needed to.