Two Data Validation Rules for a Cell based on entry in Adjacent Cell

I want to use two different data validation rules for a column of cells (Column C) based on the entry in the column of adjacent cells (Column B).
In the first case if the adjacent cell in Column B contains a number, the cell in Column C will be limited to inputting a number between 0 and 11 (Yellow highlighted cells).
In the second case if the adjacent cell in Column B is equal to zero, blank, or not a number, the cell in Column C will be limited to a number between 0 and 9,999,999.
I am not sure how to set up this to work. Any help would be appreciated.

I have attached a simple worksheet illustrating this.

A negative number is not a possibility. Thanks for your reply

Thank you very much! This custom data validation certainly does the job and accomplishes what I set out to do.

I am not familiar with the AND statement and how it is used in an IF statement. This senior citizen learns something new every day thanks to people like you.

I had to break the IF statement down so I would have a better understanding.

IF Function in Excel

Syntax: IF(logical_test, value_if_true, [value_if_false])

Custom Data Validation


=IF(B5>0,AND(C5>=0,C5<=11),AND(C5>=0,C5<=9999999))

Logical_test B5>0
Value_if_true AND (C5>=0,C5<=11)
Value_if_false AND (C5>=0,C5<=9999999))

Thank you again

Thanks for your response.

I tried your suggestion without success, maybe I copied the formula wrong. I am attaching a copy of my revised worksheet with your suggestion. Let me know what I am doing wrong!

The formula in cells C5 and C7 were not matching row numbers.
Use my formula in Post #3 above in Data validation at C5, then copy C5 and paste it in C7.

Originally Posted by TINHAMMER

I am not familiar with the AND statement and how it is used in an IF statement.

One thing to keep in mind for a custom data validation rule is that the formula must return a value of TRUE or FALSE. If it returns TRUE, then the user's value is allowed, but if FALSE then the user's value is rejected. It looks like you sorted out how the logic works but I'll walk through it anyway:

Please Login or Register  to view this content.

Thanks that did work. I appreciate your response.

One thing I did realize with both your response and that from 6StringJazzer, if I go back and change the entry in column B and don't change the entry in column C the entry in column C may not meet the Data Validation criteria.

I not sure there is any way to avoid that.

Data validation works as a response.
It will not “see” what has been entered unless you press Enter.
For a solution beyond that, a VBA solution is required.
If you dig further in search on keywords such as cascade dropdown vba, maybe you’ll find what you’re after.
Happy hunting!

Originally Posted by TINHAMMER

if I go back and change the entry in column B and don't change the entry in column C the entry in column C may not meet the Data Validation criteria.

Right, data validation is only checked at the time you enter the data in C. That's just the way it works.

If you need something more dynamic, then as Estevaoba says you need a VBA solution.

Thanks for the input.

Now that I understand that it is a response I understand how it works. I may have to breakdown and learn vba!!!

Start by reading up on the Worksheet_Change event.