Data Validation with Uppercase

[Ashvin]

Hi,


I have cell that has data validation. I created list which has values from PA001 through PA600. The data validation works great but it will accept lower case letters like "Pa100" or "pa100". I need the cell to be in uppercase. Can this be done without using VBA. Can we combine the UPPER function in the data validation process.


Thanks,

Ashvin

 

[NARAYANK991]

Hi Ashvin ,


The following link gives the answer :


http://www.cpearson.com/excel/ChangingCase.aspx


Narayan

 

[NARAYANK991]

Hi Ashvin ,


In case you still need a solution , do the following :


1. Assume your list of PA001 , PA002 ,... is from M5 through M104.


2. In any unused cell , say N5 , have the following formula : =SUMPRODUCT((H4=M5:M104)*(1*EXACT(H4,UPPER(H4))))


entered as an array formula ( with CTRL SHIFT ENTER ) , and H4 is the cell which is to be validated.


3. For H4 , in the data validation dialog box , select Custom , and use the following formula : =N5=1


4. Check or uncheck "Ignore Blank" as required.


Narayan

 

[Ashvin]

Thank you very much Narayan. The second option works great.


Thanks again,

Ashvin