Validation of cell to enter data

[gilbert]

Hi Frank,

Thanks for the superb clear explanation. You are a great teacher t
me....thanks!

Best regards,
Gilber

 

[gilbert]

Hi Frank,

There is another problem on data validation which I may need you
assistance.

Supposing I need the cell to validate that it is key in either wit
3000/xxx or 3100/xxx, isn't it that I should write it as follows?

=AND(EXACT(LEFT(B10,4),"3000"),EXACT(LEFT(B10,4),"3100"),MID(B10,5,1)="/",LEN(B10)=8,a10<>"")

I tried on but it doesn't seem working properly, even though I keyed i
3000/xxx, it still prohibit me from entering. Is there anything wron
with this formula, please advise.

Thank you.

Rgds,
Gilber

 

[JE McGimpsey]

The first four characters of B10 cannot be both exactly "3000" *AND*
exactly "3100" - at most, it can be only one of those strings, therefore
the AND will always return FALSE.

Perhaps something like:

=AND(OR(LEFT(B10,4)="3000",LEFT(B10,4)="3100"), MID(B10,5,1)="/",
LEN(B10)=8, A10<>"")

 

[Frank Kabel]

Hi
try
=AND(OR(LEFT(B10,4)="3000",LEFT(B10,4)="3100"),MID(B10,5,1)="/",LEN(B10
)=8,a10<>"")

- no need for exact when comparing numbers
- The previous formula required 3000 AND 3100 at the SAME time at the
beginning


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

There is another problem on data validation which I may need your
assistance.

Supposing I need the cell to validate that it is key in either with
3000/xxx or 3100/xxx, isn't it that I should write it as follows?

=AND(EXACT(LEFT(B10,4),"3000"),EXACT(LEFT(B10,4),"3100"),MID(B10,5,1)="

 

[gilbert]

Hi JE / Frank,

But then, without using the Exact command, it won't prevent user fro
entering small letter for the coding....my coding was structured i
such a way as 3000/A01 or 3100/A01 which the "A" must be Capita
letter. The "A" could be any alphabets ranging from A to Z.

Please advise how should I go about this

 

[JE McGimpsey]

If you're comparing A-Z, use EXACT(). Numeric digits don't require it
since they don't have an upper/lower case.

The formula in your original post compared numbers only, therefore
EXACT() wasn't needed. If you add a term to the formula that checks for
alpha characters, use EXACT only for that section. In this case, since
you're comparing a range, EXACT isn't appropriate anyway. You could do
something like:

=AND(OR(LEFT(A1,4)="3000",LEFT(A1,4)="3100"),CODE(MID(A1,5,1)>=65,
CODE(MID(A1,5,1)<=90,LEN(A1)=8,MID(A1,4,1)="/")

 

[Frank Kabel]

Hi
for this you have to add something to your formula. Try the following
(combination of the first formula and the latest post):

=AND(OR(LEFT(B10,4)="3000",LEFT(B10,4)="3100"),MID(B10,5,1)="/",LEN(B10
)=8,EXACT(MID(B10,6,1),"A"),a10<>"")

 

[gilbert]

Hi Frank,

From my understanding, this will allow only 3000/Axx or 3100/Axx (wher
xx is the numbers), right? I am afraid you have misunderstood m
requirement. The "A" is just an alphabet, which could be any characte
from A to Z. If so, I can't use the amended formula suggested, can I

 

[Frank Kabel]

Hi gilbert
if this character should be a text try
=AND(OR(LEFT(B10,4)="3000",LEFT(B10,4)="3100"),MID(B10,5,1)="/",LEN(B10
)=8,NOT(ISNUMBER(--MID(B10,6,1),"A")),a10<>"")

 

[JE McGimpsey]

Frank,

Did you test this? The formula will give a syntax error on
ISNUMBER(--MID(...),"A")

Perhaps you meant:

=AND(OR(LEFT(B10,4)="3000",LEFT(B10,4)="3100"),MID(B10,5,1)="/",LEN(B10
)=8,NOT(ISNUMBER(--MID(B10,6,1))),a10<>"")

Not sure that's what the OP wants, though, since it would allow strings
like:

3100//12

and

3100/ 12

Better to test explicitly for CODE(B10)>=65 and CODE(B10)<=90.

 

[Frank Kabel]

Hi JE
no I didn't test this -> typical error -> thanks for correcting this

I'm also not sure what the OP wants in total.
To the OP: It would be best if you may state ALL your conditions in
total so we may develop the correct formula for you. Though probably
you may be able to produce this by yourself as the technique JE and I
presented could be adapted easily
Give it a try and come back if you still have questions

 

[gilbert]

Hi Frank / JE,

Sorry to keep both of you puzzle of what my intention. My intention i
very simple. I wanted to only allow the user to key in the cod
allocated which is either 3000/Alphabet&Number&Number o
3100/Alphabet&Number&Number

The alphabet could be from A to Z (but must be in Capital Letter)
The number&number could range from 01 to 99 (must be in number only)

As I am using lookup formula for this, I don't want people to screw u
the worksheet by key in the wrong thing and as a result, rubbish i
rubbish out.

All I am looking for a formula in data validation whereby it onl
allows people to key in the correct code for the lookup table.

I hope I have managed to present to you my thoughts and hopefully, yo
will help me out with the formula.

Thank you.

Rgds,
Gilber

 

[Peo Sjoblom]

Maybe something like this

=AND(EXACT(UPPER(MID(A1,6,1)),MID(A1,6,1)),ISNUMBER(--RIGHT(A1,2)),LEN(A1),O
R(LEFT(A1,5)="3000/",LEFT(A1,5)="3100/"))

data>validation>allow>custom

Not thoroughly tested

 

[gilbert]

Hi Peo,

I have tried the formula you suggested, it is not working either. Jus
like the one suggested by JE, it will allow 3000//01 or 3000/001 o
3000/$01 to go through.

I think we are almost there...just that how can we ensure that th
sixth character is an Capital Alphabet and not anything else.

Thank you.

Rgds,
Gilber

 

[JE McGimpsey]

Try:

=AND(OR(LEFT(A1,5)="3000/",LEFT(A1,5)="3100/"),
CODE(MID(A1,6,1))>=65, CODE(MID(A1,6,1))<=90, LEN(A1)=8,
ISNUMBER(-MID(A1,7,1)), ISNUMBER(-RIGHT(A1,1)))

 

[gilbert]

Hi JE,

Have you tried the formula?...it seems like something is amisse
here.....Excel keep on prompting for error when I tried to enter th
formula...I tried to correct by changing here and there but still no
successful. Can you help me with this?

Thank you.

Rgds,
Gilber

 

[Peo Sjoblom]

I tried J.E.'s formula and it works, validation is a bit sensitive so
you have to get the whole formula in one line or it will kick back.
Put the formula in notepad first, turn off wordwrap.
put the cursor at the end of the top line and press delete to move up the
second line, repeat
until the formula is in one line. Now select it and copy and paste it into
validation (or change the cell reference first)

 

[gilbert]

Yes...it works now...maybe I made a stupid mistake just now.....sorry J
and thanks to your help....and also Peo's clarification.

BTW, what is the reason for the negative sign when we used ISNUMBE
function? Is it a must for every ISNUMBER function we used?

Also, where can we get all the CODE (I believe it is ASCII Cod
table)...is there a listing that I could copy or download so as for m
future reference in case I need to use CODE function?

Thank you.

Rgds,
Gilber

 

[Frank Kabel]

Hi
as the MID function returns a text value the minus sign tries to
convert this text into a number. ISNUMBER checks if this conversion was
successful. Without this ISNUMBER would always return FALSE as it would
only check text values returned by MID

 

[Peo Sjoblom]

Put characters in let's say A1:A52 a-z and A-Z
in B1 put

=CODE(A1)

copy down