custom data validation on cells with data validation values

[AKrobbins]

I am trying to write a custom data validation for cells with data
validation values: The cell content in column A is restricted to
values 1 and 2. The cell content in Column B is restricted to values
1, 2, 3, 4, 98, or 97. Value 97 in column B is only suppose to be used
when the value in Column A is 2, so I would like to have a warning
sign when a value other than 97 is entered in Column B when the value
in Column A is 2. I input this formula in the data validation custom
field: =AND(A1=2,B2<>97) and tested it by entering 2 in column A and
4 in Column B and no warning message appeared. Is there another
formula that should be used when attempting a custom data validation
on cells with data validation values.

Thank you,

Athena

 

[AKrobbins]

I am trying to write a custom data validation for cells with data
validation values: The cell content in column A is restricted to
values 1 and 2. The cell content in Column B is restricted to values
1, 2, 3, 4, 98, or 97. Value 97 in column B is only suppose to be used
when the value in Column A is 2, so I would like to have a warning
sign when a value other than 97 is entered in Column B when the value
in Column A is 2. I input this formula in the data validation custom
field:  =AND(A1=2,B2<>97) and tested it by entering 2 in column A and
4 in Column B and no warning message appeared. Is there another
formula that should be used when attempting a custom data validation
on cells with data validation values.

Thank you,

Athena

I meant to note the cells are noncontiguous, the data in column B is
in column C (my apologies).

 

[Gord Dibben]

Look at your logic at little closer and you will see why you do not get any
error message.

You have allowed B2 to be anything but 97 if A1 is 2

=AND(A1=2,B2=97) is what you want.


Gord Dibben MS Excel MVP