"Tricky Situation" - Validation Formula

[Teddy-B]

Hi Everyone: Thanks for helping!

I need a validation formula that will allow input into a cell based upon the
data in a different cell. For Example: I want excel to allow only the word
"cash" to be entered in B1 if the word "apple" is in A1. If a word other than
"apple" is in A1, then excel will allow only the word "credit" to be entered.

Thanks again!

 

[Ron Coderre]

Since you are only allowing two values, it seems like you need a conditional
function, not data validation.

Perhaps something like this:
B1: =IF(A1<>"",IF(A1="apple","cash","credit"),"")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Teddy-B]

Not Exactly...
That works great but I'm afraid that i was not completely honest in my
ultimate intentions. I have to use validation because in a different set of
colums on the same worksheet, I want to allow "Cash" or "Check" if
d1="apples" and I want to allow only "Credit" if d1 does not ="apples".

Please respond and Thanks:

 

[Ron Coderre]

Try something like this:

G1: Cash
G2: Check

H1: Credit

Select the cell A1

<data><validation>
Allow: List
Source: =IF(D1="apples",$G$1:$G$2,$H$1)
Click [OK]

Note: If D1 contains "apples", the user selects either "Cash" or "Check",
then
changes "apples" to something else....A1 will still indicate the previous
choice. You'd need VBA to clear A1's contents if D1 changes.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[RAGdyer]

This works for me:

=OR(AND(A1="apple",B1="cash"),AND(A1<>"apple",A1<>"",B1="credit"))

Make sure that "Ignore Blank" is *UNCHECKED*!