Conditional Data Validation

W

Walt Herman

Hi! I am trying to enforce selection from a list (Range1) in column D when
the value in column c is equal to "Hello", if the value in column c does not
equal "Hello" I want the user to be able to type in whatever they want in
column D. I have pored through the newsgroup and not found anything relevant,
hence this question...Thanks...
 
B

Bob Phillips

Make sure that the C cell has the value Hello in it, then add this formula
to the DV type of List

=IF(C1="Hello",rng)

where rng is the range of your list

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
W

Walt Herman

Bob, thank you for your prompt response. I have already gotten that far. The
problem is when column C does not equal "Hello" I want the user to be able to
enter whatever they want in column D and validation seems to be enforcing ""
 
P

Peo Sjoblom

You have to follow Bob's directions, it does what you want.

--


Regards,


Peo Sjoblom
 
W

Walt Herman

It works if the next row of column C is blank as it will then allow you to
type text in freeform in column D. However, if there is any string in that
cell of column c the validation will disallow any other entry in column D. My
column C will always have text values in it and occasionally they will
contain "Hello" but more often than not they will not.

I appreciate your help nonetheless. Thanks.
 
P

Peo Sjoblom

You are right, the reason it works is because it ignores blanks unless it is
unchecked. AFAIK you cannot fix this since you would be using conditions
which are opposite to each other

The condition to display the list is that C1 equals "Hello", then you cannot
also have a condition
that says something else. Meaning that validation block entries that return
FALSE so if C1 is not "Hello" but has some sort of entry the formula returns
FALSE thus blocking any other entry


--


Regards,


Peo Sjoblom
 
Top