Data Validation w/If Statement

[Jeff Gross]

I searched through previous posts and thought I had found a similar
situation, but it is not working still so I thought I would post myself.

I have a drop-down list in cell E7 with two options to pick from - STEL, PBZ.

I have a data validation in cell E9 that needs to require that if STEL is
picked, then the value entered must be 30; otherwise if PBZ is picked, then
any number is acceptable.

I tried the following formula as my data validation and unchecked "ignore
blank":

=AND((E5="STEL"),(E7=30))

Apparently something is wrong because it doesn't matter what is picked in
cell e5, it requires that the cell be 30 all the time even if PBZ is picked.

What am I missing on this? It seems so simple?

Help.

 

[Peo Sjoblom]

The problem is that validation is triggered by TRUE or FALSE or 1 or 0,
you would need VBA for this since if you select STEL from the dropdown in E7
then
that condition is TRUE and if PBZ is selected the condition is FALSE thus it
will block any entry
since it is not STEL. Btw in your example you are not using E9 but E5 I
assume that is a typo?


--


Regards,


Peo Sjoblom

 

[Jeff Gross]

Can you help me with the code or should I post in programming?

Jeff

 

[T. Valko]

You can use a formula like this:

=OR(AND(E7="stel",E9=30),AND(E7="pbz",ISNUMBER(E9),E9<=100^100))

However, you probably still will want an event macro to clear the cell (E9)
when a different selection is made. For example, if you select "PBZ" and
enter 100 in E9 then you change your mind and select "STEL" but the 100 is
still entered in cell E9 and 100 is an invalid number for "STEL".