Validation Formula

[Teddy-B]

Hi everyone. I need a validation formula that will allow (only) the word
"CHIPS" in the validation cell if cell A1<>"MB". If Cell A1="MB", then the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"

 

[T. Valko]

Try this:

=OR(AND(K10<>"MB",M10="Chips"),AND(K10="MB",OR(M10="Cash",M10="Check",M10="Chips")))

 

[Teddy-B]

Thanks T.Valko, but this did not work. I am setting custom validation for
cell M10. when I entered your formula as validation, excel would not allow me
to enter anything into the cell (M10).
I need to set the following validation requirements in cell (M10):

IF K10 = "MB" then M10 can only be Cash,Chips,or Check.
and
If K10 <>"MB" then M10 can only be Chips.

 

[David Biddulph]

What did you enter in K10, and what did you try to enter in M10?

In what way do you think that T Valko's formula differs from your
requirement? It seems to do exactly what you asked for. Perhaps you'd
better check that you've entered the formula correctly. Did you copy and
paste, or did you try to retype it?

Note that if you enter a valid entry in M10 but then change K10, the M10
validation condition will not be checked again until you try to edit M10
again, so you may wish to impose a validation condition on K10 too.

 

[Debra Dalgleish]

On another worksheet, create two lists, one with chips, and another with
all three items.
Name each list, e.g. ChipsList and PayList
There are naming instructions here:
http://www.contextures.com/xlNames01.html

In the data validation cell, choose Data>Validation
For Allow, select List
For the Source, enter the formula:
=IF($A$1="MB",PayList,ChipsList)

 

[Teddy-B]

Thanks for keeping me straight David. Also, Thanks T.Valko for the formula -
It works well.

 

[Teddy-B]

Thanks for keeping me straight David. Thanks T. Valko for the formula. When
plugged in properly, it works well.

 

[T. Valko]

Glad you got it straightened out. Thanks for the feedback!