Validation of cell to enter data

[gilbert]

Can anybody tell me how shld I go about it if I wanted Excel to verif
that Column A is entered with a series of number say, Pxxx (eg. A1
P001, A2 - P002, A3 - P003, etc) before allowing the user to ente
values in column B (B1, B2, B3, etc). That is to say, if any one cel
in column A is blank or not following the series starting with P, th
user cannot key in a value in the corresponding cell in column B.

Please help

 

[Frank Kabel]

Hi
try using data validation ('Data - Validation).:
- Select the cells in column A (assumption you start in A1)
- enter the following formula in the data validation dialog
=AND(LEN(A1)=4,LEFT(A1,1)="P))

- select your cells in column B
- enter the following formula in the data validation dialog
=A1<>""

 

[gilbert]

Thanks Frank....

Err...just another question....how are you going to create two
validation for example, in a particular cell, I want it to validate
that it is a date and also I want it to validate if the cell before
that cell has been keyed in. How shall I go about it? In another word,
even if C1 is a date, if B1 is empty, the user will not be able to key
in any value for C1...can we do that?

 

[Frank Kabel]

Hi
Combination of two criteria is normally no problem. In your case the
check for a date is difficult (as for Excel this is a number and there
is no function like ISDATE). The best thing I can come up with is:
Enter the following formula in the validation dialog
=AND(B1<>"",ISNUMBER(C1))

 

[gilbert]

Hi Frank...

Gee...that's too bad....coz my cell c1 is actually restricting a dat
range..so I have to use a date function (currently I am usin
date\betwee\=date(a1,1,1) & =date(a1,12,31) where a1 is the yea
defined

Coz I don't want people to simply key in a date that is out of thi
year. And I also must make sure people key in the cell before thi
(also a date) before they are allow to key in the date.

Is there no other way we could do it?



Rgds,
Gilbert :

 

[Frank Kabel]

Hi
this won't prevent entering just numbers
try the following formula
=AND(B1<>"",C1>=DATE(A1,1,1),C1<=DATE(A1,12,31))

 

[gilbert]

Hi Frank,

Back to my very first question

I tried to use data validation (=A1<>"") in cell B1 to prevent peopl
from entering data if A1 is blank but it seems no working, I wonde
why? I tried to let A1 blank and I still can key in data in B1

 

[gilbert]

Hi Frank,

Sorry...my mistakes...apologize. I did not uncheck the "ignore blank
checkbox. So, we have to uncheck that box in order for Excel to chec
for blank cells....However, it won't work for the dates formulas tha
you suggested. I tried to play around with the symbol "<, >," als
don't work. it will prompt if the cell before it is blank, but if h
cell before is filled up, than it will prompt also even thought I hav
enter the dates within the range.....any further thoughts?

Thank you.


Rgds,
Gilber

 

[Frank Kabel]

Hi
in which cell do you enter your year A1 or B1. You may have to change
the formula accordingly. Otherwise it should work

 

[gilbert]

Hi Frank,

ok..ok...my careless again, I refer the wrong cell...sorry.

Thanks for the useful help. I have made my worksheet more lively now.

Thanks.

Rgds,
Gilber

 

[Frank Kabel]

Hi Gilbert
so we finally got there - good to hear and thanks for the feedback

 

[gilbert]

Hi,

It's me again....still stuck with data validation function.

I have learned from this forum experts how to validate a cell that i
is enter with specific requirements, such as starting with capital P
My question now is...how do I go about if I need the cell to b
validate with few letter as well as symbol? Just take an example
suppose I need the user to enter a series starting with ABxxxx/xxxC
(total 12 characters). Both the AB and CD must be in Capital letter
and the 'obliged' (/) must be there as well....any thoughts

 

[Frank Kabel]

Hi
now it's getting a little bit more complicated
Try the following formula in the data validation dialog (if A1 is the
cell to check):
=AND(CODE(LEFT(A1,1))=65,CODE(MID(A1,2,1))=66,CODE(MID(A1,11,1))=67,COD
E(RIGHT(A1,1))=68,MID(A1,7,1)="/",LEN(A1)=12)

 

[JE McGimpsey]

A little bit less complicated:

=AND(EXACT(LEFT(A1,2),"AB"),EXACT(RIGHT(A1,2),"CD"),MID(A1,7,1)="/")

 

[Frank Kabel]

Hi JE
thanks for that - forgot about EXACT

But the OP may add the lenght validation to your formula:
=AND(EXACT(LEFT(A1,2),"AB"),EXACT(RIGHT(A1,2),"CD"),MID(A1,7,1)="/",LEN
(A1)=12)

 

[JE McGimpsey]

Yup - missed the 12 char spec. Thanks for the correction.

 

[gilbert]

Gee...thanks to both of you, Frank and JE.

Err...Frank, actually...mind to explain what is the first formul
about. It is indeed a complicated one, with 65,66,67. What is it al
abount. Maybe this time I may not need it...but who knows some othe
time. Please elaborate a bit.

Thanks.

Best regards,
Gilber

 

[gilbert]

Hi JE / Frank,

Mind if I ask a silly question, why do we need to a "1" in the MI
function? Is it telling Excel that to verify only 1 character, that i
the seventh character? If so, why we need not define for LEFT functio
and RIGHT function?

=AND(EXACT(LEFT(A1,2),"AB"),EXACT(RIGHT(A1,2),"CD"),MID(A1,7,1)="/",LEN(A1)=12)

Just curious to understand further...hope you will share.

Thank you.

Rgds,
Gilber

 

[Frank Kabel]

Hi Gilbert
I checked the actual ASCII Code of the characters (to distinguish 'A'
from 'a'). So 65 is the ASCII Code of the letter 'A' (97 would be the
code fopr 'a'). As said before I forgot the EXACT function

 

[Frank Kabel]

Hi Gilbert
The LEFT function returns only n characters from the left (e.g.
LEFT("abcde",2) = "ab"). The RIGHT function does the same for
characters starting at bthe end of a string.
If you want to check characters in the middle of a string (as this is
the case for checking '/' in your specific formula) you have to use
MID:
MID(string, starting point, lenght). So MID(A1,7,1) returns a string
starting at the 7th position with the lenght of 1.