data validation lists

[[email protected]]

Hi,
I am trying to use data validation lists.... with a small twist.
For simple validations, I have no trouble creating the list, or setting
up the validation because that's pretty standard. Right now, though, I
want to do something a little nonstandard.

I have a validation list that consists of a code and a description.
So, for a list of states I would fill the validation list with stuff
like 'NY New York' where 'NY' is the state code, and 'New York' is a
description of what the code means.

I create a validation list with all the state codes and their full
names, and everything is fine. When the user clicks on the pull down
list they see a list of states and their names.

But, when the pull down is NOT ACTIVE, I want the cell to show only the
state code (NY) and not the complete contents of the validation list
(NY New York).

Seems like it should be possible, but I'm definitely not an excel
expert, so this is something I haven't figured out yet. Any ideas?

Thanks in advance...
Brad

[Debra Dalgleish]

There's a sample workbook on my web site that shows a product name and
code in the Data Validation dropdown list. After an item is selected,
the cell shows only the product name. You may be able to adapt this to
your workbook.

On the following page:

http://www.contextures.com/excelfiles.html

Under the data validation heading, look for 'Data Validation "Columns"


[email protected] wrote:
> Hi,
> I am trying to use data validation lists.... with a small twist.
> For simple validations, I have no trouble creating the list, or setting
> up the validation because that's pretty standard. Right now, though, I
> want to do something a little nonstandard.
>
> I have a validation list that consists of a code and a description.
> So, for a list of states I would fill the validation list with stuff
> like 'NY New York' where 'NY' is the state code, and 'New York' is a
> description of what the code means.
>
> I create a validation list with all the state codes and their full
> names, and everything is fine. When the user clicks on the pull down
> list they see a list of states and their names.
>
> But, when the pull down is NOT ACTIVE, I want the cell to show only the
> state code (NY) and not the complete contents of the validation list
> (NY New York).
>
> Seems like it should be possible, but I'm definitely not an excel
> expert, so this is something I haven't figured out yet. Any ideas?
>
> Thanks in advance...
> Brad
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[[email protected]]

I took a very quick look and I think this is exactly what I want. I
noticed you answer quite a few excel questions, and wanted to tell you
that I appreciate your willingness to help!

Thanks !

Brad Vernon

[Debra Dalgleish]

You're welcome! If you have any trouble modifying the code, post another
question, and someone will probably be able to help.

[email protected] wrote:
> I took a very quick look and I think this is exactly what I want. I
> noticed you answer quite a few excel questions, and wanted to tell you
> that I appreciate your willingness to help!
>
> Thanks !
>
> Brad Vernon
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[[email protected]]

Debra,
I had no trouble getting your code to work in my situation. Thank you.
There is a new twist that I would like to resolve.


In most types of pull down lists you can enter a character and have the
list automatically advance to a matching entry. I don't get this type
of behavior. Also, if, in your example, I simply wanted to type the
word Desk rather than select it from the pull down I would get a
validation error. My supervisor noticed that my code exhibited this
undesirable characteristic. Have you put together a list that would
allow either selection via pick from list or typein of the value (desk)
itself? Note that if I type in 'Desk -- ID# 40732' (per your example)
it would work. But, clicking in this cell fails the edit test
later.....


Another way of looking at this is to have the validation itself key on
'Desk', but have the pull down DISPLAY the longer text "Desk -- ID#
40732". Then typing desk would work.... any ideas?

Regards, and thanks again,
Brad

[Debra Dalgleish]

You could combine the product/code technique with the combobox overlay,
described here:

http://www.contextures.com/xlDataVal11.html

[email protected] wrote:
> Debra,
> I had no trouble getting your code to work in my situation. Thank you.
> There is a new twist that I would like to resolve.
>
>
> In most types of pull down lists you can enter a character and have the
> list automatically advance to a matching entry. I don't get this type
> of behavior. Also, if, in your example, I simply wanted to type the
> word Desk rather than select it from the pull down I would get a
> validation error. My supervisor noticed that my code exhibited this
> undesirable characteristic. Have you put together a list that would
> allow either selection via pick from list or typein of the value (desk)
> itself? Note that if I type in 'Desk -- ID# 40732' (per your example)
> it would work. But, clicking in this cell fails the edit test
> later.....
>
>
> Another way of looking at this is to have the validation itself key on
> 'Desk', but have the pull down DISPLAY the longer text "Desk -- ID#
> 40732". Then typing desk would work.... any ideas?
>
> Regards, and thanks again,
> Brad
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html