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

On Jun 21, 10:49*am, AKrobbins <athenakrobb...@gmail.com> wrote:
> 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).

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

On Tue, 21 Jun 2011 07:49:03 -0700 (PDT), AKrobbins <(E-Mail Removed)>
wrote:

>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