Data Validation Question

[Please Help]

Hello,

I have created a data validation for Cell H16 to make sure that the value in
that cell is A, B or C. It works, but it does not check for case
sensitivity. How can I make it to check to make sure that it checks for
upper case, in addition to the correct value in that cell?

I use "Custom" Data Validation, and below is the formula I use.

=OR(H16="A",H16="B",H16="C")

Thanks.

 

[Peo Sjoblom]

You can use

=OR(EXACT(H16,"A"), and so on

however the easiest way would be to select list from allow as opposed to
custom and type in

A,B,C

in the box, then clear in cell dropdown

That is much easier and the list values are case sensitive


--


Regards,


Peo Sjoblom

 

[Please Help]

Peo,

Thanks for your help. I just tried
"=or(exact(h16,"A"),exact(h16,"B"),exact(h16,"C"))", and I got an error
message about formula contains error.

I have also tried the List, and it would not work for me because it would
cause me problems when users type in the value with leading spaces (e.g. "
A").

Thanks.

 

[Peo Sjoblom]

It works fine for me, I even copied what you posted
and it works
Btw, I don't understand what you mean that it wouldn't work if
a user types a leading space, the formula wouldn't work either with a
leading space
unless you use TRIM

the list version works exactly the same as the formula version


--


Regards,


Peo Sjoblom

 

[Please Help]

Peo,

Thanks for the follow-up. For some reason, before I replied to you, the
formula didn't work. After I replied to you, I tried again and it worked.

As far as using the List, it would cause me problems on my adjacent cell
because the list does not validate the leading spaces, and my adjacent cell
is relied on that validating cell. I can not have any leading spaces in the
data validation field; otherwise, my adjacent cell would not work.

Thanks again.