Hi Luke
It works fine for me. If you want to give your email address, I can mail
you the test sheet I set up.
I suspect that you have one of the formulae entered incorrectly.
You can test them to find out which is wrong, by going to
InsertNameDefine select First, then go to Refers to pane then press
the F2 key.
Now, mark the whole of the formula and copy (Ctrl-C), press Escape, then
paste the formula into any blank cell on your sheet.
This will show up which formula is failing, and is therefore entered
incorrectly.
Regards
Roger Govier
Luke wrote:
Hi Roger
Thanks for the help but I am having a few problems. I have entered all the
named formulae but when I enter
=AND(First,Second,Number,Last)
in the data validation and try to OK it, it comes up with The Formula
currently evaluates to an error. Do you wish to continue?
Any ideas?
Many thanks
Luke
"Roger Govier" wrote:
Hi Luke
Basically there are a number of things you need to check with regard to
NI Code.
The first character must be Alpha, and the second character. I'm not
certain whether the first of these falls within a specific range, e.g. X
to Z as most NI numbers I have seen begin Y. Anyway, I am just testing
here that it is uppercase, A to Z, but that can easily be narrowed as
appropriate.
The way I would tackle it is as follows. Create 4 named formulae, to do
the various testing.
InsertNameDefine and put each of the following Names in the name pane
and the relevant formulae in the Refers to pane.
First =AND(CODE(MID($A2,1,1))64,CODE(MID($A2,1,1))<91)
Second =AND(CODE(MID($A2,2,1))64,CODE(MID($A2,2,1))<91)
Number =AND(LEN($A1)=9,ISNUMBER(--MID($A1,3,6)))
Last =AND(CODE(RIGHT($A8,1))64,CODE(RIGHT($A8,1))<68)
Then use Data Validation, mark your range of cells where you are going
to input the codes, and then
DataValidation choose dropdown to Select Custom, then in the Formula
pane paste the following code
=AND(First,Second,Number,Last)
Then click on the Error Tab and enter an appropriate message to inform
the user about the type of data that is acceptable, and click OK.
I have used the MID function for both First and Second, as it was easier
just to alter one number to create each code, rather than use Left for
one and Mid for the other.
Code(A) is 65, so testing for 64 and <91 accepts capital A to Z in
First and Second, and 64 and <68 limits it to A to D for Last.
If the values for the first 2 characters is in the range of X to Z, then
amend the values of Code in First and Second as appropriate.
Regards
Roger Govier
Luke wrote:
Hi
Is it possible to only allow data of a certain format to be entered in to a
cell. For instance a UK national insurance number that always has the same
format of two letters followed by six numbers and then either A, B, C or D
i.e YB123456A.
Many thanks for your help
Luke
|