Excel Combo Box - Parts Linked to Prices

[Hal2003]

I'm trying to develop a spreadsheet with several combo boxes, with multiple part numbers in each combo box, that will link to and display the price of the diplayed part above each combo box.

For example; "Books" has twenty options, "Pencils" has five options, "Pens" has nine options. The user would then pick a book, pencil and pen from each combo box.

Once the desired book, pencil and pen have been selected, Excel will link those parts with their respective prices and then display that price for each at the top of the combo box and then perform a simple sum of the three item prices for a final price.

If anyone can help, I'd greatly appreciate it.

 

[Zathras]

This is easy to do but hard to describe.

Assuming you are using the control toolbox to get the combos and dropping them on the sheet (View / Toolbars / Control Toolbox)...

Start with a blank sheet and set up as follows:
Widen columns A,B,C
Drop combo boxes on A3, B3 and C3
Put Book list and prices in columns E2:F21 (I always put column headers in row 1)
Put Pencil list and prices in columns G2:H6
Put Pen list and prices in columns I2:I10
Put in these formulas:
A1: =VLOOKUP(A2,E2:F21,2,0)
B1: =VLOOKUP(B2,G2:H6,2,0)
C1: =VLOOKUP(C2,I2:J10,2,0)
D1: =SUM(A1:C1)

Set up text box properties:
ComboBox1.LinkedCell = A2
ComboBox1.ListFillRange = E2:E21
ComboBox2.LinkedCell = B2
ComboBox2.ListFillRange = G2:G6
ComboBox3.LinkedCell = C2
ComboBox3.ListFillRange = I2:I10

Toggle out of design mode (Top-left icon on the Control Toolbox toolbar -- looks like a triangle/t-square/pencil)

If you did everything correctly, you should be able to select an item from each combo and see the total in cell D1.