case sensetive data validation

G

Guest

I have a worksheet where the costumer types in data. The data is later
used for data validation as a list.

My big problem is, that data validation based on a list is not Case
sensetive. This means data can be added that is not exactly the same
case "Yes" ><"yes", etc.

The applied data data is exported to another program, that is case
sensetive.

Using upper case is not an option. I need to control that the user only
types in exact valid data.

Any ideas?
 
B

Bob Phillips

You could try a custom DV type with a formula of

=EXACT(D1,INDEX(F1:F10,MATCH(D1,F1:F10,0)))

where D1 is the DV cell, F1:F10 is the validation list.

--
HTH

Bob Phillips

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

Max

One way

Assuming the valid list [case sensitive] is housed in E1:E10 (eg: Yes, No,
etc)
with inputs to be made in col A

Select col A,
Click Data > Validation
Allow: Custom
Formula:
=SUMPRODUCT(--ISNUMBER(FIND(A1,$E$1:$E$10)))>0
Click OK

Test it out
 
D

Debra Dalgleish

If the list isn't too long, type it in the data validation dialog box,
instead of referring to a list on the worksheet. Then it will be case
sensitive.
 
G

Guest

Thats not an option, because the validation lists are dynamic. The
users are allowed to change the lists. And some of the lists are long.
First they fill in sheet1 containing the lists for validation.
Then then fill in sheets 2-10 containing cells with data validation
based on the lists in sheet one.

As for the other posts they give me a true/false and I have no spare
cells to show them in.
 
B

Bob Phillips

DV should return True or False, that is how DV knows whether to accept it or
to throw an error.

--
HTH

Bob Phillips

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