Excel Combo Box Question

[myeyesdeceive]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

So here is what I am trying to do. I have an excel workbook with a few sheets. On sheet "Data" I have 3 columns of information, product item number in column A, item desc in column B and item Price in column C. On sheet "Form" I am trying to make a Combo Box that Displays the Item Number from Sheet "Data" (Column A) in the actual drop down and the Price (sheet "data" column C) as my I believe "cell link" result. I have created the Combo Box and it does show the item numbers in the drop down but the result in the adjoining cell is just the number corresponding to which item number you selected row in sheet "data".

I really do not have any programing knowledge for visual basic so I can't work very much in there. I know that in the help file you can put formulas in the "Cell Link" box (which currently only has $I$23 in it which is where i wish my item cost would go) and was hoping someone can point me in the right direction here.

This sheet had some sort of functioning way of doing this on a windows based excel using active X of sorts I believe because it sure doesn't work on mac.

I have been going crazy over this any help would be extremely appreciated!!!
Mike

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

So here is what I am trying to do. I have an excel workbook with a few sheets.
On sheet "Data" I have 3 columns of information, product item number in column
A, item desc in column B and item Price in column C. On sheet "Form" I am
trying to make a Combo Box that Displays the Item Number from Sheet "Data"
(Column A) in the actual drop down and the Price (sheet "data" column C) as my
I believe "cell link" result. I have created the Combo Box and it does show
the item numbers in the drop down but the result in the adjoining cell is just
the number corresponding to which item number you selected row in sheet
"data".

I really do not have any programing knowledge for visual basic so I can't work
very much in there. I know that in the help file you can put formulas in the
"Cell Link" box (which currently only has $I$23 in it which is where i wish my
item cost would go) and was hoping someone can point me in the right direction
here.

This sheet had some sort of functioning way of doing this on a windows based
excel using active X of sorts I believe because it sure doesn't work on mac.

I have been going crazy over this any help would be extremely appreciated!!!
Mike

You don't need visual basic to do this. The combo box produces an index into
the range in the linked cell. Use a formula =index(data!a1:a100,linkedcell)
This formula will display the product number. You can use a similar formula
to display the description and price using the same linked cell as the index
number.

 

[myeyesdeceive]

Bob,

Thank you for the quick response. I think I am still doing something wrong. I tried using your formula for the "Cell Link" input in the Format object for my Combo Box with a few mods that I needed (or thought I did!).

Here is what I have so far:

Input Range: Data!$A:$C (data is sheet only with data)
A Column is Item Number which I want inside the drop box, C column is costs which I want to populate outside of the box in a separate cell.

Cell Link: =index(Data!a1:a138,$G$2)

It allows me to continue without an error. When I try and select a value from the drop down nothing populates in Cell G2 on Data sheet. When I go back in to check the cell link in format object it is blank. Any thoughts?

 

[myeyesdeceive]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

So here is what I am trying to do. I have an excel workbook with a few sheets.
On sheet "Data" I have 3 columns of information, product item number in column
A, item desc in column B and item Price in column C. On sheet "Form" I am
trying to make a Combo Box that Displays the Item Number from Sheet "Data"
(Column A) in the actual drop down and the Price (sheet "data" column C) as my
I believe "cell link" result. I have created the Combo Box and it does show
the item numbers in the drop down but the result in the adjoining cell is just
the number corresponding to which item number you selected row in sheet
"data".

I really do not have any programing knowledge for visual basic so I can't work
very much in there. I know that in the help file you can put formulas in the
"Cell Link" box (which currently only has $I$23 in it which is where i wish my
item cost would go) and was hoping someone can point me in the right direction
here.

This sheet had some sort of functioning way of doing this on a windows based
excel using active X of sorts I believe because it sure doesn't work on mac.

I have been going crazy over this any help would be extremely appreciated!!!
Mike

You don't need visual basic to do this. The combo box produces an index into
the range in the linked cell. Use a formula =index(data!a1:a100,linkedcell)
This formula will display the product number. You can use a similar formula
to display the description and price using the same linked cell as the index
number.
[/QUOTE]

Bob,

Thank you for the quick response. I think I am still doing something wrong. I tried using your formula for the "Cell Link" input in the Format object for my Combo Box with a few mods that I needed (or thought I did!).

Here is what I have so far:

Input Range: Data!$A:$C (data is sheet only with data)
A Column is Item Number which I want inside the drop box, C column is costs which I want to populate outside of the box in a separate cell.

Cell Link: =index(Data!a1:a138,$G$2)

It allows me to continue without an error. When I try and select a value from the drop down nothing populates in Cell G2 on Data sheet. When I go back in to check the cell link in format object it is blank. Any thoughts?

 

[Bob Greenblatt]

Bob,

Thank you for the quick response. I think I am still doing something wrong. I
tried using your formula for the "Cell Link" input in the Format object for my
Combo Box with a few mods that I needed (or thought I did!).

Here is what I have so far:

Input Range: Data!$A:$C (data is sheet only with data)
A Column is Item Number which I want inside the drop box, C column is costs
which I want to populate outside of the box in a separate cell.

Cell Link: =index(Data!a1:a138,$G$2)

It allows me to continue without an error. When I try and select a value from
the drop down nothing populates in Cell G2 on Data sheet. When I go back in to
check the cell link in format object it is blank. Any thoughts?

NO, this is not right. You combo box should reference the list, and a single
cell, the linked cell, which will contain the index of the selected value.
So, to display a combo box with the item number, the combo box should
reference Data A1:A138, and have a cell link to $g$32. Now, when you select
something, G32 will change to the index value.

Now, to display that item number in a cell, that cell needs the index
formula you stated above.

 

[myeyesdeceive]

Bob,

Thank you for the quick response. I think I am still doing something wrong. I
tried using your formula for the "Cell Link" input in the Format object for my
Combo Box with a few mods that I needed (or thought I did!).

Here is what I have so far:

Input Range: Data!$A:$C (data is sheet only with data)
A Column is Item Number which I want inside the drop box, C column is costs
which I want to populate outside of the box in a separate cell.

Cell Link: =index(Data!a1:a138,$G$2)

It allows me to continue without an error. When I try and select a value from
the drop down nothing populates in Cell G2 on Data sheet. When I go back in to
check the cell link in format object it is blank. Any thoughts?

NO, this is not right. You combo box should reference the list, and a single
cell, the linked cell, which will contain the index of the selected value.
So, to display a combo box with the item number, the combo box should
reference Data A1:A138, and have a cell link to $g$32. Now, when you select
something, G32 will change to the index value.

Now, to display that item number in a cell, that cell needs the index
formula you stated above.
[/QUOTE]

Bob Thank you I have it working now. One last quick question. A few people asked if I could also have the Item Desc/Item Price show up inside the dropdown instead of just the item number. Is this possible? I have all 3 columns selected in the input range (Data!$A:$C). Any thoughts?
Sorry I didn't include this in my last reply, they just asked this morning!

Mike

 

[Bob Greenblatt]

Bob Thank you I have it working now. One last quick question. A few people
asked if I could also have the Item Desc/Item Price show up inside the
dropdown instead of just the item number. Is this possible? I have all 3
columns selected in the input range (Data!$A:$C). Any thoughts?
Sorry I didn't include this in my last reply, they just asked this morning!

Mike

I think that may work in Windows, but I have not tested it. It does not work
on the Mac. You will have to use 3 separate drop downs, or better yet, 3
index formulas keying off the index from the first drop down. Or a single
cell containing =Index(a1:a138,$g$32)&" "&Index(b1:b138,$g$32)&"
"&index(c1:c138,$g$2)

 

[myeyesdeceive]

Perfect, I used 2 more index formulas and that works great. thanks for all the help and super fast responses!

Mike