Validation customized cell("B9")

[Maurizio]

Hi all,
I need a formula to Validation "B9" cell:

i.e.
if into B8 there is number "1" or "2" then into B9 I can not put anything

but

if B8 is 3 or 4 or 5 or 6 then into B9 I can put in only 1 or 2


Tia. Maurizio

 

[dave2018]

Here is a solution, but it requires you have a additional sheet in your workbook.

add another sheet, put 1 in A1, put 2 in A2.

select them and in the name box, type dvrng

select B9 on the first sheet, and under the list option, put this formula:

=IF(OR($B$8=3,$B$8=4,$B$8=5,$B$8=6),dvrng)

Note: if you type a value, say 2, in B9, and THEN change B8 to a 1, then B9 stays the same, unless you press delete...


(this is a simple solution that works, but i know it's not the best way to do it, so let's see what the experts say)

 

[Eric W]

Dave's solution works, but doesn't necessarily require another sheet. You can put the range on the same sheet, just outside of viewing range, or you can hide it.

Another option is to select Custom from the Allow: box and enter this formula:

=AND(OR(B8=3,B8=4,B8=5),OR(B9=1,B9=2))

You don't get the drop-down box in this case, but you can use the Error Alert tab to create a message explaining what values can be typed in. With only 2 numbers, that's not too bad.

Also, Data Validation can easily be defeated. You can copy a different cell and paste it into B9 and it will override the DV.

 

[dave2018]

Dave's solution works, but doesn't necessarily require another sheet. You can put the range on the same sheet, just outside of viewing range, or you can hide it.

. it just makes me comfortable to have another sheet. not sure why i imposed that on him...

 

[Joe4]

Also, Data Validation can easily be defeated. You can copy a different cell and paste it into B9 and it will override the DV.

For that reason, VBA might be a better option, as it is harder to defeat.
(They could disable macros, but there are ways to render the workbook virtually unusable if they do).

 

[Maurizio]

Dave's solution works, but doesn't necessarily require another sheet. You can put the range on the same sheet, just outside of viewing range, or you can hide it.

Another option is to select Custom from the Allow: box and enter this formula:

=AND(OR(B8=3,B8=4,B8=5),OR(B9=1,B9=2))

You don't get the drop-down box in this case, but you can use the Error Alert tab to create a message explaining what values can be typed in. With only 2 numbers, that's not too bad.

Also, Data Validation can easily be defeated. You can copy a different cell and paste it into B9 and it will override the DV.

Perfect!!!
many thanks everybody.

 

[Eric W]

Glad we could help. Let us know if you have follow-up questions.

 

[Maurizio]

Glad we could help. Let us know if you have follow-up questions.

And in this case:

if into B8 there is number "1" then into B9 I can input only "2"

but

if into B8 there is number "2" then into B9 I can input "3"

otherwise into B9 I can not put anything


Thnks
Maurizio

 

[Eric W]

Is this in addition to the previous rules, or instead of?

 

[Maurizio]

Is this in addition to the previous rules, or instead of?

--> instead of.

Thnks