Allowing data in specific forms

L

Luke

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
 
M

Max

One play which might suffice ..

Set-up a defined columnar range in say, X1:X4
---------------
Input in X1:X4 the letters: A, B, C, D
Select X1:X4, and click inside the namebox
(the droplist just to the left of the equal sign)
and type: List

(Note that the defined range: List can be set-up on another sheet)

Suppose the col to be formatted is col A
Select col A (select the col header)
Click Data > Validation
Select Custom under "Allow" droplist
Put in "Formula:" box
=AND(LEN(A1)=9,ISNUMBER(MID(A1,3,6)+0),ISNUMBER(MATCH(RIGHT(A1,1),List,0)))
Click OK
 
R

Roger Govier

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.
Insert>Name>Define 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
Data>Validation 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
 
L

Luke

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
 
R

Roger Govier

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
Insert>Name>Define 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
 
M

Max

Hi Roger,

I'm not sure if there were some typos in the defined names listed in your
first response:
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)
Click to expand...

Should all cell refs read above as: "$A1" ?
(think there's some cell refs reading: $A2, $A1, $A8)

And I don't know why, but I also found that I could only get your suggestion
working properly over here (I'm using Excel 97) by putting the whole string
of formulas together in the "Formula:" box in the DV (with col A selected):

=AND(CODE(MID($A1,1,1))>64,CODE(MID($A1,1,1))<91,CODE(MID($A1,2,1))>64,CODE(
MID($A1,2,1))<91,LEN($A1)=9,ISNUMBER(--MID($A1,3,6)),CODE(RIGHT($A1,1))>64,C
ODE(RIGHT($A1,1))<68)

Anyway, thought your suggestion was by far, the better one ..

Perhaps you could send a copy of your test file over ?
To: demechanik <at> yahoo <dot> com

Thanks
 
R

Roger Govier

Hi Max (&Luke)

Many apologies to you both. What I posted was incorrect, as you rightly
point out Max, all references should be to $A1

First =AND(CODE(MID($A1,1,1))>64,CODE(MID($A1,1,1))<91)
Second =AND(CODE(MID($A1,2,1))>64,CODE(MID($A1,2,1))<91)
Last =AND(CODE(RIGHT($A1,1))>64,CODE(RIGHT($A1,1))<68)
Number =AND(LEN($A1)=9,ISNUMBER(--MID($A1,3,6)))

These are the formulae I used when setting up my sheet, but when I was
copying the formulae back as text to various cells for copying into my
response, Excel changed the references dependent upon the cell in which
I was located when I went to Insert>Name>Define.
I have never noticed this behavior before. I have noticed it change
references to something obscure, like B65536, when it should be A1,
which is why I put the dollar before A, the row having to remain
relative for use down the column.

I guess I have usually been defining dynamic ranges with Name in the
past, where the cell references are always totally Absolute.

I will have to watch out for this in the future.
Thanks for bringing it to my attention Max. A copy of my file is on its
way to you.

Regards

Roger Govier
 
R

Roger Govier

Hi Luke

Copy of file sent directly to you.

Regards

Roger Govier


Thanks very much for your help Roger. Please could i have a copy of your
test file as well. My email is [email protected]

Many thanks

Luke

:


Hi Max (&Luke)

Many apologies to you both. What I posted was incorrect, as you rightly
point out Max, all references should be to $A1

First =AND(CODE(MID($A1,1,1))>64,CODE(MID($A1,1,1))<91)
Second =AND(CODE(MID($A1,2,1))>64,CODE(MID($A1,2,1))<91)
Last =AND(CODE(RIGHT($A1,1))>64,CODE(RIGHT($A1,1))<68)
Number =AND(LEN($A1)=9,ISNUMBER(--MID($A1,3,6)))

These are the formulae I used when setting up my sheet, but when I was
copying the formulae back as text to various cells for copying into my
response, Excel changed the references dependent upon the cell in which
I was located when I went to Insert>Name>Define.
I have never noticed this behavior before. I have noticed it change
references to something obscure, like B65536, when it should be A1,
which is why I put the dollar before A, the row having to remain
relative for use down the column.

I guess I have usually been defining dynamic ranges with Name in the
past, where the cell references are always totally Absolute.

I will have to watch out for this in the future.
Thanks for bringing it to my attention Max. A copy of my file is on its
way to you.

Regards

Roger Govier



Max wrote:
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Max

Shows up as attached in my sent items file. I have forwarded it again,
and sent a brand new message from Outlook with it attached as well.
Mail me back directly if you don't receive.

Regards

Roger Govier
 
Top