Validation customized cell("B9")

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
674
Office Version
  1. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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

Board Regular
Joined
Mar 20, 2018
Messages
223
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 15, 2002
Messages
674
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640

ADVERTISEMENT

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

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
674
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
Is this in addition to the previous rules, or instead of?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top