Data Validation

[stak]

Hi Everyone

I have a spreadsheet that allows people to put data into
Cells A1, A2, A3, A4 and A5.

If they enter "Y" into cell A1, then I want to block them
from entering anything into cells A2, A3, A4 and A5.

However, if they enter something into A2, A3, A4 or A5 or
all of them, then I want to block them from entering
anything in cell A1.

I have tried Data Validation but to no avail.
Please, please can anyone help and advise.
Many thanks.
Sue

 

[Paul]

Hi Everyone

I have a spreadsheet that allows people to put data into
Cells A1, A2, A3, A4 and A5.

If they enter "Y" into cell A1, then I want to block them
from entering anything into cells A2, A3, A4 and A5.

However, if they enter something into A2, A3, A4 or A5 or
all of them, then I want to block them from entering
anything in cell A1.

I have tried Data Validation but to no avail.
Please, please can anyone help and advise.
Many thanks.
Sue

Try this:
Select A1:A5.
Data > Validation
Allow: Custom
Formula: =(COUNTA($A$1:$A$5)<=1)

 

[Leo Heuser]

Hi Sue

What do you want to happen, if people
enter anything else but "Y" in A1 while
A2:A5 is blank?

 

[stak]

Hi Leo

Basically, if I enter Y in A1, then I want to make sure
that I cannot enter Y in any of the other cells. However,
if I enter Y in any one of the other 4 cells (A2:A5)or all
of them, then I want to make sure that I cannot enter
anything into cell A1.

I hope this makes sense. Thanking you in advanced.
Sue

 

[Guest]

Hi Paul. Thanx for your help. It works if I only want to
enter a Y in 1 cell. I had that formula. However, what I
need is if I enter Y in A1, then all the others (A2:A5)
are locked out. But, if I enter Y in any one of the
others (A2:A5) or all of them, then I want to lock out A1.

Hope this makes sense. Thanking you in advance.
Regards, Sue

 

[Leo Heuser]

Hi Sue

I don't think, I got an answer to my question.

If e.g. X is entered in A1 (A2:A5 empty),
and then Y is entered in e.g. A4, what will
happen then? I mean Y in A4 should prevent
anything from being entered in A1, but A1
allready has a value!? Are you looking for
a solution, which in this situation will blank A1?

 

[Guest]

Hi Leo

If Y is entered into A4 first, then A1 is locked out
automatically. The user cannot enter anything into A1 if
they have already put something in A2, A3, A4 or A5.

If they put in Y into A1 first, then all other cells are
locked out.

Hope this is clearer.

Thank you.

Sue

 

[Leo Heuser]

Hi Sue

I may have taken your use of "Y" literally, so here
is a solution, which works this way:

If *anything* is entered in A1, it's not possible
to enter *anything* in A2:A5
and
if *anything* is entered in one or more cells in
A2:A5, it's not possible to enter *anything* in A1.

1. Select A1:A5
2. Choose Data > Data validation
3. In "Allow" choose "User defined" (or similar)
4. Enter this formula as one line:

=IF($A$1<>"",COUNTA($A$2:$A$5)=0,
IF(COUNTA($A$2:$A$5)>=1,$A$1=""))