On 6/26/03 06:43, olly did expound most eloquently:

> Hi Guys
> Ok i've set up a spreadsheet so that a user can select a product
> number from a drop down list, with the drop down list values coming
> from the 2nd sheet in the work book. But what i want it to do is to,
> on a user choosing an order number, i also want excel to lookup from
> sheet 2 the corresponding product name, and place it in a cell in the
> product column in sheet 1 (i.e. next to the combo box). So basically,
> then when a user selects a product code from the already working combo
> box, excel will auto fill in the product name it corresponds to, in
> the adjacent cell. I realise this is probably very easy, but i can't
> think how to, can any1 help me?
> Any feedback gr8ly appreciated
> Olly


One way you can do this:

For the dropdown, assign a cell where the returned value will be placed
(that is, assign a cell for Cell Link on the Control tab of Format Control).
This result is a number from 1 to the number of items in your product list.
I'd also suggest naming the range where your product names are, if you've
not already done so; I'll assume a name of "product_names". (I'll also
assume the cell used for the Cell Link is named as "result_code".)

In the cell next to the dropdown, place this formula:

=IF(result_code="","",INDEX(product_names,result_code))

Until a selection is made, the cell next to the dropdown will be blank; the
value is filled in when a selection is made.

If you wish, you can even have the result (the Cell Link cell) placed into
the cell that is immediately behind your dropdown; the dropdown remains in
front of the cell so the value is not visible.

If you have more than one dropdown, you may not want to name the ranges, but
the principle is the same.


George

--
Mac Word FAQ: <http://www.mvps.org/word/FAQs/WordMac/index.html>
Entourage Help Page: <http://www.entourage.mvps.org/>
Or try Google: <http://www.google.com>

Please do not reply to this message. Although the email address is perfectly
valid, any replies to this account never get to the Inbox on my computer.

George Clark <clark_george[at]hotmail.com> wrote in message news:<BB205DC6.5C4EC%clark_george[at]hotmail.com>...
> On 6/26/03 06:43, olly did expound most eloquently:
>
> > Hi Guys
> > Ok i've set up a spreadsheet so that a user can select a product
> > number from a drop down list, with the drop down list values coming
> > from the 2nd sheet in the work book. But what i want it to do is to,
> > on a user choosing an order number, i also want excel to lookup from
> > sheet 2 the corresponding product name, and place it in a cell in the
> > product column in sheet 1 (i.e. next to the combo box). So basically,
> > then when a user selects a product code from the already working combo
> > box, excel will auto fill in the product name it corresponds to, in
> > the adjacent cell. I realise this is probably very easy, but i can't
> > think how to, can any1 help me?
> > Any feedback gr8ly appreciated
> > Olly
>
>
> One way you can do this:
>
> For the dropdown, assign a cell where the returned value will be placed
> (that is, assign a cell for Cell Link on the Control tab of Format Control).
> This result is a number from 1 to the number of items in your product list.
> I'd also suggest naming the range where your product names are, if you've
> not already done so; I'll assume a name of "product_names". (I'll also
> assume the cell used for the Cell Link is named as "result_code".)
>
> In the cell next to the dropdown, place this formula:
>
> =IF(result_code="","",INDEX(product_names,result_code))
>
> Until a selection is made, the cell next to the dropdown will be blank; the
> value is filled in when a selection is made.
>
> If you wish, you can even have the result (the Cell Link cell) placed into
> the cell that is immediately behind your dropdown; the dropdown remains in
> front of the cell so the value is not visible.
>
> If you have more than one dropdown, you may not want to name the ranges, but
> the principle is the same.
>
>
> George



Excellent-worked a treat, thanks very much George.
Regards
Olly