Case Sensitive Data Validation

[D_E_Manton]

Hi Everyone,

I'm developing a tool for analysing educational data which involves the input and the analysis in one workbook. I am controlling the input with data validation using a named range because of the amount of times I would have to repeat the same delimited list when I create the other validation sheets/cells.

However because I am using a named range for this data validation it is not case sensitive. This means that later on when I use the named ranges based on dependency to select filters for my data the conditional statements that do the maths won't recognise an input which deviates from the named range. This unfortunately messes it up.

Therefore is it possible to make data validation with named ranges case sensitive? I would prefer a non macro solution but if it has to be it will have to be.

Thanks in advance.

 

[AlphaFrog]

In the formulas that reference the DV cells, could you correct the case using the functions; LOWER, UPPER, or PROPER ?

 

[D_E_Manton]

Because they need to input in the cells then the input would surely replace those formulas? Or are you talking about using a macro?

Would this work in my conditional formulas as a way of comparing the values?

such as =IF(Lower(Cell Ref) = Lower(Cell Ref),Do if true, do if false)

 

[barry houdini]

Which conditional statements are you using? Most Excel functions are not case-sensitive, e.g. a direct comparison like

=A1=B1

will return TRUE

even if A1 = "APPLE" and B1 = "apple".

Having said that you could make the data validation case-sensitive by using EXACT function, e.g. with a custom formula

=SUM(EXACT(A1,List)+0)

where A1 is your data validation cell and List is the name of the list. That method won't allow you to use a dropdown list, though