Data Validation -- Uppercase List

[dtrimble]

In one of my documents, I am using a data validation list to require selection from a pre-defined list. That part is easy enough.

However, if the user enters a matching value in lower or mixed case, I want the spreadsheet to automatically convert it to uppercase, which is the standard used in the defined list. I don't want to prompt the user with an error if the case is wrong; I'd like this spreadsheet to be smart enough to convert it automatically.

VBA is not an option on this document; I have to be able to do it with data validation only.

Is this possible? I've tried combinations of =UPPER(=ListName) in the custom formula field under Data Validation, but I'm not getting it to accept my formula.


dt

[Richard Buttrey]

Why is VBA not an option? That would be the simplest, and I believe only way of achieving what you want.

[dtrimble]

Why is VBA not an option? That would be the simplest, and I believe only way of achieving what you want.

Organization's requirements/limitations, not mine. Is there no way to do this without VBA?

[Richard Buttrey]

Organization's requirements/limitations, not mine. Is there no way to do this without VBA?

Not that I know of. The nearest you could do is use an =UPPER(A1) in another cell and use that other cell in any subsequent analysis.
But just out of interest why does your organisation require that VBA not be used? Why avoid such a powerful tool particularly when it's the only way of achieving what you want.

Would you also change your location to something meaningful please. We as this for a reason since knowing this we can imply certain standards, like for instance date formats. US posters are odd and have a strange way of expressing dates as mm/dd/yyyy It helps to know this.

[dtrimble]

Not that I know of. The nearest you could do is use an =UPPER(A1) in another cell and use that other cell in any subsequent analysis.
But just out of interest why does your organisation require that VBA not be used? Why avoid such a powerful tool particularly when it's the only way of achieving what you want.

Would you also change your location to something meaningful please. We as this for a reason since knowing this we can imply certain standards, like for instance date formats. US posters are odd and have a strange way of expressing dates as mm/dd/yyyy It helps to know this.

The organization considers any use of VBA to be a security risk. I don't get this POV either, but it's what I have to work with.

[Richard Buttrey]

Hi,

Without VBA therefore you can't achieve what you want in the way you want.
The nearest you can probably get is to accept whatever is input and then use =UPPER(A1) to convert afterwards.