Excel 2007 : Calculate via list box options

[Cushionsupplier]

Hi Everyone

Another newbie here.

I have what should be a simple worksheet that will calculate the cost of a cushion based on three dimension and a square foot price. My problem is, I want to basically get all this info into one line using a list box. For instance, i have a list box containing approx 6 types of filling ie Feather, Fibre and seveal grades of foam. I have another list box that list quantities 1 through to 10. each filling has its own price.

So, what i want to do is have my customers first select a filling then enter manually the sizes ( 3 cell containing random numbers chosen by the customer), the quantity from a list box.
Cell A1 Cell B1 Cell C1 Cell D1 Cell E1 Cell F1
Filling Inches Inches Inches Quantity Price
Example: Feather 12 12 1 1 £0.92

The price in cell F1 is based on cell F1 =B1*B2*D1/144*E1*0.92

The 0.92 in the formula is the square foot price of a feather cushion.

So, the filling needs to be linked with a set sq ft price and the quantity which are all going to be list boxes where they can be calulated according to user input

If anyone can help a novies with this it would be appreciated.

[NBVC]

So do you have a separate list, showing the available cushions and associate price/sq.ft?

If yes, then you can get the 0.92 with a Vlookup that references that list.

e.g. =B1*B2*D1/144*E1*Vlookup(B1,$X$1:$Y$10,2,FALSE)

where B1 contains cushion chosen and X1:Y10 contains table of prices...

[Cushionsupplier]

Thanks for the quick reply NBVC.

Yes i have 3 lists. List 1 contains fillings, List 2 contains sq ft price, List 3 contains quantities require 1 through to 10.

I will take your well appreciated advice and let you know the results.

Thanks

[NBVC]

You will need a list showing the prices for each cushion type for the Vlookup though...

[Cushionsupplier]

I have a list of prices, thanks again.

[Cushionsupplier]

I have entered the following

=O2*F2*G2*H2/144*I2*VLOOKUP(O2,$M$1:$M$2,2,FALSE)

O2 contains the product name
F2 G2 H2 all contain user input (numbers)
I2 contains quantity (number)
$M$1:$M$2 contain prices

the result is #value

can you tell me where i am going wrong please

Thanks in advance

[shg]

See Help for VLOOKUP. If the matched item is in col M, and the price in is col N, but your table is only one col wide (M1:M2)

[NBVC]

Are there only two cushions? There should be cushion names in L1:L2, then you would use the Vlookup as: =Vlookup(O2,$L$2:$M$2,2,False)

[Cushionsupplier]

Thank you for your time and patience, I am obviously doing something wrong as this still returns a #value.

At the moment there are 9 product names listed in column O and, 2 prices listed in column M (more prices will be added to column at a later time).

Would it be possible to show the worksheet in here for you to look at or perhaps email the worksheet to you.

[NBVC]

Well, since your filler list in column O and prices are in column M, then Vlookup() won't work for you since Vlookup requires the lookup column to be left of the extraction column... so instead we will use an INDEX/MATCH combination...

... but you have to have a price for each type of filling in the same row as the filling.. see attached...

... also, in your formula the first variable is O2, which is a text value and so you can't multiply anything by it... hence the #Value error...

I took it out, but I don't know if something else should be there instead...

See also my INdex/Match formula to extra item based on selection in A1

=F2*G2*H2/144*I2*INDEX(M1:M10,MATCH(A1,Filling,0))

[Cushionsupplier]

You are a star...........that's exactly what i wanted to do. Thank you so much.