Data validation allows non capitals through, even though not specified in drop-down list

[barcrest_j]

Hi

My first post on the forums.

I have created a named range to be used as a source for an in-cell drop down list. The named range consists of 4 values "D"; "E"; "S"; and "T". I have then used that named range as a source list for data validation purposes. But Excel still allows the user to enter the small letter equivalents of these through without displaying the error message.

From my perspective I am either stuck with having a custom formula as my data validation or potentially allowing an incorrect value by using the drop-down.

My custom formula would be =AND(COUNTIF(CapitalsOnly,A1),EXACT(UPPER(A1),A1))

Where CapitalsOnly is the named range as above.

Am I right that there is no way round this without VBA?

Thanks for reading.

b_j

[XOR LX]

Hi,

Your data validation formula looks perfectly good to me. I tried to recreate it and it prohibited - as expected - the entry of lower case letters.

If you still can't get it to work, it may be that you need to upload a small sample.

Regards

[barcrest_j]

Thank you for the reply. I don't think I explained myself properly.

What I am after is to have a data validation type of List and to point it at the CapitalsOnly named range. However, if I do this then the user can not only select D, E, S or T from the list they can also key into the cell directly d, e, s or t and bypass the data validation; i.e. it does not error and allows the small letters through even though they are not in the named list.

I can get around this using the data validation type of Custom and then using my custom formula, but then I lose the in cell drop-down list.

It appears I cannot have a drop down list that disallows small letters.

[XOR LX]

Ah, my apologies.

And I assume that you don't wish to lose the flexibility of having a dynamic source list by typing in a comma-separated delimited list (which is case-sensitive) for your validation?

If so, then unfortunately you are correct that this is not achievable, at least without VBA, and I trust that someone with a bit more expertise in that area than I will pick up this thread and get back to you.

Regards