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))<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
--