Formula to Calculate Optional Color Cost Based on Size of Product Selected Not Working

**Hube2002** · 03-13-2012, 08:51 PM

SGCS Dealer Order 5.0.xlsx

I need help with this formula, I think i maybe don't have the data set right. What I need is for when a customer selects a model trailer (we sell enclosed cargo trailers) and adds a color the sheet will calculate the cost of color depending on size of trailer purchased. For example if a customer selects a 6x10 trailer and then chooses black color which is $4 per foot I need it to calculate it at $40. If the customer changes his selection to a 14' trailer I need the cost of the black color to change with it. It calculates with the formula I have but it is wrong and it does not change after the initial selection. If the customer chooses a different size the original miscalculation stays the same.

The Sheet is attached, If anyone is willing to help I would appreciate it and would like to learn the solution also, what I'm doing wrong.

Thanks in advance,

Hubert Smith
South Georgia Cargo Sales

**Russell Dawson** · 03-13-2012, 11:57 PM

In "Quote Sheet" in L13 your VLOOKUP refers to "Sheet1!" for the data array but Sheet 1 is not there and the data does not appear anywhere else..

**Hube2002** · 03-15-2012, 12:11 AM

Okay, I got it to Select the correct color and display the price per foot of color but i do not know how to make it calculate the feet of color based on the size of the trailer chosen. It will display black color as $8 but if i select a 10ft trailer it does not calculate the black color at a quantity f 10 to equal the $80 that it should. Any help, Please

Revised Sheet is attached,SGCS Dealer Order 5.0.xlsx