Data Validation Problem

[kashif.special2005]

Hi,

I am facing problem in data validation, I have a name range called "State_Unique" (Name Range Address:= A1:A300), all values in the name range are in capital letters, now in a cell in other sheet, I applied a data validation from that name range ("State_Unique"), it is working fine when user select value from the drop down, but the problem is the values in the drop down are showing in capital letters, and when I type that drop down value in small letters in that cell, it is accepting.

Like, value shows in the drop down "AL" and when I type al, it is accepting, which is I don't want, I want that user select value from the drop down only.

Please help me to resolve that problem.

Thanks
Kashif

 

[Domenic]

If you can do without having a dropdown list from which to choose, you can use a custom formula instead. This means that the user will always have to manually type a value in the cell. So, for example, to apply the data validation for C1...

Allow:  Custom

Formula:  =MATCH(TRUE,EXACT(State_Unique,C1),0)

Hope this helps!

 

[kashif.special2005]

Hi Domenic,

Thanks for reply, however I need drop down data validation, because user does not know the state code.

Thanks
Kashif

 

[Domenic]

Have you thought about using an ActiveX combobox?

 

[kashif.special2005]

I thought about it, but the validation will apply in many cells, for that validation would be better option.


Thanks
Kashif

 

[Domenic]

Maybe you can use the technique described here...

http://www.contextures.com/xlDataVal14.html#Create

Basically, you add a single combobox to your worksheet, and then use VBA to make it appear in cells that contain Data Validation.

 

[kashif.special2005]

Hi Domenic,

Thanks for your kind help , it helps me solve this problem.


Thanks
Kashif

 

[Domenic]

You're very welcome!