How do I put combo box values into cells (without using @CHOOSE)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LongTermNoob - 08 Mar 2007 12:06 GMT

I am using a combo box to select from a very long list. In order to save
repeating the process of selecting from the combo box on each sheet in the
workbook I would like to transfer the value displaid in the first combo into
a cell on each of the other sheets. The list is too long to use the @CHOOSE
function.

Any ideas?

Thanks.

Reply to this Message

vezerid - 08 Mar 2007 12:33 GMT

On Mar 8, 2:06 pm, LongTermNoob
<LongTermN...@discussions.microsoft.com> wrote:

> I am using a combo box to select from a very long list. In order to save
> repeating the process of selecting from the combo box on each sheet in the
[quoted text clipped - 5 lines]
>
> Thanks.

Go to a cell in another worksheet where you would want your value
transfered. Type =. Without cancelling the editing process, navigate
to the sheet with the combo box and point to the linked cell. Click
and press Enter. Repeat for the other sheets. If it is the same cell
(e.g. F3) in all other sheets, then you can select all the other
shifts (Shift+click on the sheet tab) and do the process once.

HTH
Kostis Vezerides

Reply to this Message

LongTermNoob - 08 Mar 2007 13:04 GMT

Thanks, but that copies the corresponding list value, not the actual value in
the combo box, and I would then need to use @CHOOSE to get the corresponding
data. As I said - the list is way too long for that!

> On Mar 8, 2:06 pm, LongTermNoob
> <LongTermN...@discussions.microsoft.com> wrote:
[quoted text clipped - 17 lines]
> HTH
> Kostis Vezerides

Reply to this Message

vezerid - 08 Mar 2007 13:25 GMT

On Mar 8, 3:04 pm, LongTermNoob
<LongTermN...@discussions.microsoft.com> wrote:

> Thanks, but that copies the corresponding list value, not the actual value in
> the combo box, and I would then need to use @CHOOSE to get the corresponding
[quoted text clipped - 21 lines]
> > HTH
> > Kostis Vezerides

Ahh, I see. CHOOSE is one option. But you can also use VLOOKUP. You
can have a column of numbers (1, 2, 3,...) before your range that
contains the labels of your combo box. Then, if a combo box is linked
to a cell, say F3, where the index number of the label is stored, you
can retrieve the label with:

=VLOOKUP(F3,Labels!A:B,2,0)

Here we assume that the labels are in sheet called Labels and that the
values were moved to column B:B after inserting the numbers in column
A:A.

Does this help?
Kostis

Reply to this Message

LongTermNoob - 08 Mar 2007 13:44 GMT

Yes it does. I hadn't considered using VLOOKUP. I have some awkward mods to
do to my table, but it should work fine.

Thanks for the help,

> On Mar 8, 3:04 pm, LongTermNoob
> <LongTermN...@discussions.microsoft.com> wrote:
[quoted text clipped - 38 lines]
> Does this help?
> Kostis

Reply to this Message