|
Allowing data in specific forms
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 |
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(MA TCH(RIGHT(A1,1),List,0))) Click OK -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Luke" wrote in message ... 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 |
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 |
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 |
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 |
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) 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,C ODE(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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
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 InsertNameDefine. 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: 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) 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Hi Luke
Copy of file sent directly to you. Regards Roger Govier Luke wrote: Thanks very much for your help Roger. Please could i have a copy of your test file as well. My email is Many thanks Luke "Roger Govier" wrote: 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 InsertNameDefine. 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: 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) 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))<9 1,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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
"Roger Govier" wrote:
.. A copy of my file is on its way to you. Received your email, Roger, but the file wasn't attached -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
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 Max wrote: "Roger Govier" wrote: .. A copy of my file is on its way to you. Received your email, Roger, but the file wasn't attached -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Mail me back directly if you don't receive ..
I received your forward (but again w/o any file) but not the new message. I've replied direct 24 hrs ago but the reply mail probably lost itself in cyberspace ?? If it's not too much trouble, perhaps you could post a link here to d/l your test file. Amongst the free filehosts that I know of/use include: http://flypicture.com/ http://cjoint.com/index.php (this one was suggested by Bob Phillips) http://www.savefile.com/index.php -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Hi Max
I don't know what is going on with direct mails between us. Anyway, I have uploaded the file to http://flypicture.com?display=updone&id=rdjxlanY I took a look at the Cjoint link, but it was all in French. Bob lives closer to the English Channel than me, so is obviously much better versed in French than I.<vbg Now if it was in Welsh ....<g Regards Roger Govier Max wrote: Mail me back directly if you don't receive .. I received your forward (but again w/o any file) but not the new message. I've replied direct 24 hrs ago but the reply mail probably lost itself in cyberspace ?? If it's not too much trouble, perhaps you could post a link here to d/l your test file. Amongst the free filehosts that I know of/use include: http://flypicture.com/ http://cjoint.com/index.php (this one was suggested by Bob Phillips) http://www.savefile.com/index.php -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Thanks, Roger ! I've got the file
I took a look at the Cjoint link, but it was all in French .. Hey, that's what I told Bob, too <bg, but I kinda figured out the steps since: Just click "Browse" button, navigate to folder select the file Open then click the button centred in the page below ("Creer le lien Cjoint") and it'll generate the link ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Hi Max
Glad you've got the file. Wasn't expecting a French lesson this morning, the brain is addled enough already<bg Thanks for the info, I have followed your instructions and it works fine. Regards Roger Govier Max wrote: Thanks, Roger ! I've got the file I took a look at the Cjoint link, but it was all in French .. Hey, that's what I told Bob, too <bg, but I kinda figured out the steps since: Just click "Browse" button, navigate to folder select the file Open then click the button centred in the page below ("Creer le lien Cjoint") and it'll generate the link ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Cheers, Roger !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
| All times are GMT +1. The time now is 07:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com