Drop down data validation

Hi

I know how to create drop down lists in data validation but I am looking to
get a little extra. Currently I create a list in A1:A10 create a range name
for the list and then use that named range in the data validation screen.
However, what I want now is:

Create a list in A1:A10 and a description in B1:B10 for example:

A B
1001 Coffee
1002 Tea
1003 Hot Chocolate

I would like the to see both the name and the description in the drop down
but once selected only select the data from column A. Don't know if this
can be done, but any help would be appreciated.

Thanks
Stephen

You could use the Active-X Control Combobox;

On your spreadsheet - Go (at the menu) View, Toolbars, Control Toolbox;
1) Drag a ComboBox to a clear space.
2) Click on the Properties icon on toolbox - Prop Sheet shoud come up.
3) Fill In the following settings:
a) ListFillRange (G1:H10) ' your lookup range
b) LinkedCell: A1 - Where you want your choice dropped
c) Column Count: 2 ' your # of columns to show
d) BoundColumn: 1 ' the column Number you want to select to post

Click on the TOP LEFT ICON (the design-mode) to turn-off and make
The Combo box available for use;
Give it a try

Hope this helps

"Stephen Reid" <[email protected]> wrote in message
news:[email protected]:

> Hi
>
> I know how to create drop down lists in data validation but I am looking to
> get a little extra. Currently I create a list in A1:A10 create a range name
> for the list and then use that named range in the data validation screen.
> However, what I want now is:
>
> Create a list in A1:A10 and a description in B1:B10 for example:
>
> A B
> 1001 Coffee
> 1002 Tea
> 1003 Hot Chocolate
>
> I would like the to see both the name and the description in the drop down
> but once selected only select the data from column A. Don't know if this
> can be done, but any help would be appreciated.
>
> Thanks
> Stephen

have a named range that is actually A1&" "&B1
when you select your item use say vlookup(result,a1:a10,1,false)
--
paul
[email protected]
remove nospam for email addy!



"Stephen Reid" wrote:

> Hi
>
> I know how to create drop down lists in data validation but I am looking to
> get a little extra. Currently I create a list in A1:A10 create a range name
> for the list and then use that named range in the data validation screen.
> However, what I want now is:
>
> Create a list in A1:A10 and a description in B1:B10 for example:
>
> A B
> 1001 Coffee
> 1002 Tea
> 1003 Hot Chocolate
>
> I would like the to see both the name and the description in the drop down
> but once selected only select the data from column A. Don't know if this
> can be done, but any help would be appreciated.
>
> Thanks
> Stephen
>
>
>

Thanks Jim, I had thought about this but wasn't 100% sure how to do it.


"Jim May" <[email protected]> wrote in message
news:bm1yg.104337$IZ2.88916@dukeread07...
> You could use the Active-X Control Combobox;
>
> On your spreadsheet - Go (at the menu) View, Toolbars, Control Toolbox;
> 1) Drag a ComboBox to a clear space.
> 2) Click on the Properties icon on toolbox - Prop Sheet shoud come up.
> 3) Fill In the following settings:
> a) ListFillRange (G1:H10) ' your lookup range
> b) LinkedCell: A1 - Where you want your choice dropped
> c) Column Count: 2 ' your # of columns to show
> d) BoundColumn: 1 ' the column Number you want to select to post
>
> Click on the TOP LEFT ICON (the design-mode) to turn-off and make
> The Combo box available for use;
> Give it a try
>
> Hope this helps
>
> "Stephen Reid" <[email protected]> wrote in message
> news:[email protected]:
>
>> Hi
>>
>> I know how to create drop down lists in data validation but I am looking
>> to
>> get a little extra. Currently I create a list in A1:A10 create a range
>> name
>> for the list and then use that named range in the data validation screen.
>> However, what I want now is:
>>
>> Create a list in A1:A10 and a description in B1:B10 for example:
>>
>> A B
>> 1001 Coffee
>> 1002 Tea
>> 1003 Hot Chocolate
>>
>> I would like the to see both the name and the description in the drop
>> down
>> but once selected only select the data from column A. Don't know if this
>> can be done, but any help would be appreciated.
>>
>> Thanks
>> Stephen
>

Hi Paul,

Thanks for your reply, I had tried playing around with this kind of solution
but couldn't getting it working. Would you mind going into a bit more
detail?


"paul" <[email protected]> wrote in message
news:[email protected]...
> have a named range that is actually A1&" "&B1
> when you select your item use say vlookup(result,a1:a10,1,false)
> --
> paul
> [email protected]
> remove nospam for email addy!
>
>
>
> "Stephen Reid" wrote:
>
>> Hi
>>
>> I know how to create drop down lists in data validation but I am looking
>> to
>> get a little extra. Currently I create a list in A1:A10 create a range
>> name
>> for the list and then use that named range in the data validation screen.
>> However, what I want now is:
>>
>> Create a list in A1:A10 and a description in B1:B10 for example:
>>
>> A B
>> 1001 Coffee
>> 1002 Tea
>> 1003 Hot Chocolate
>>
>> I would like the to see both the name and the description in the drop
>> down
>> but once selected only select the data from column A. Don't know if this
>> can be done, but any help would be appreciated.
>>
>> Thanks
>> Stephen
>>
>>
>>

You can do this with programming. There's a sample file here:

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


Under Data Validation, look for 'DV0005 - Data Validation "Columns"'


Stephen Reid wrote:
> Hi
>
> I know how to create drop down lists in data validation but I am looking to
> get a little extra. Currently I create a list in A1:A10 create a range name
> for the list and then use that named range in the data validation screen.
> However, what I want now is:
>
> Create a list in A1:A10 and a description in B1:B10 for example:
>
> A B
> 1001 Coffee
> 1002 Tea
> 1003 Hot Chocolate
>
> I would like the to see both the name and the description in the drop down
> but once selected only select the data from column A. Don't know if this
> can be done, but any help would be appreciated.
>
> Thanks
> Stephen
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html