Creating Custom Product SKU and Pricing

[squarepants]

We manufacture products based on roughly 8 items that make up a single product. Each item has a description, cost and sale price. Our goal is to create a SKU generator and price calculator based on these different product options.
A sample SKU might look like G1-A4-B9-H4-M5-J2-R6-K5 with a total cost of $1.00 and a sale price of $2.00.
A user would select from 8 drop-down menus to select the items that will make up this product in order to arrive at the final SKU, price and cost
They might select "Apples" from the first drop down, which would give them the "G1" portion of the SKU listed above and add $0.10 to the cost and $0.20 to the sale price
Next they would select "Red" from the second drop down which would give them the "A4" portion of the SKU listed above and add $0.10 to the cost and $0.20 to the sale price

This process would repeat for all eight items to create the final SKU. I'm no Excel expert, though I'm fairly certain this is easy lifting for Excel. Please help if you can. Thank you in advance.

 

[BenMiller]

Sounds pretty easy. Two questions:
1) Will the options available in the second drop-down be affected by the selection in the first one; and so on?
2) Does the price per final items depend on the combination of the items selected, or is it just a sum of the total; meaning each items has a cost price and a sale price, and the final is just the sum of that?

 

[squarepants]

This will be just a total sum of each item without dependencies. There is one exception. In the "K5" part of the SKU, this will be a hand entered length of the custom product. So if the user enters 500, it will multiply the per meter cost and sale price of the item represented in the "B9" part of the SKU. For instance, if "B9" is Copper Cable at $0.30 per meter and the person enters a total of 500 meters for the "K5" value (let's just replace "K5" with 500, since that's what it should be), then the cost for this product will increase by $150.00. This will be added to the other items also selected. The others will all be static, without further calculation. Hope this helps and thank you for the speedy reply.

 

[BenMiller]

Easiest way is to create a look-up table in another section of your workbook with all the prices for each item. Let's say it's on Sheet2 in columns A through C (SKU, price, cost).
In the "Total price" cell on Sheet1, type

=VLOOKUP([COLOR=#333333]G1,Sheet2!A:B,2,0)+[/COLOR]VLOOKUP([COLOR=#333333]A4[/COLOR][COLOR=#333333],Sheet2!A:B,2,0)+([/COLOR]VLOOKUP([COLOR=#333333]B9[/COLOR][COLOR=#333333],Sheet2!A:B,2,0)*K5)+[/COLOR]VLOOKUP([COLOR=#333333]H4[/COLOR][COLOR=#333333],Sheet2!A:B,2,0)+[/COLOR]VLOOKUP([COLOR=#333333]M5[/COLOR][COLOR=#333333],Sheet2!A:B,2,0)+[/COLOR]VLOOKUP([COLOR=#333333]J2[/COLOR][COLOR=#333333],Sheet2!A:B,2,0)+[/COLOR]VLOOKUP([COLOR=#333333]R6[/COLOR][COLOR=#333333],Sheet2!A:B,2,0)[/COLOR]

In the "Total cost" cell on Sheet1, type

=VLOOKUP([COLOR=#333333]G1,Sheet2!A:C,3,0)+[/COLOR]VLOOKUP([COLOR=#333333]A4[/COLOR][COLOR=#333333],Sheet2![/COLOR][COLOR=#333333]A:C,3[/COLOR][COLOR=#333333],0)+([/COLOR]VLOOKUP([COLOR=#333333]B9[/COLOR][COLOR=#333333],Sheet2![/COLOR][COLOR=#333333]A:C,3[/COLOR][COLOR=#333333],0)*K5)+[/COLOR]VLOOKUP([COLOR=#333333]H4[/COLOR][COLOR=#333333],Sheet2![/COLOR][COLOR=#333333]A:C,3[/COLOR][COLOR=#333333],0)+[/COLOR]VLOOKUP([COLOR=#333333]M5[/COLOR][COLOR=#333333],Sheet2![/COLOR][COLOR=#333333]A:C,3[/COLOR][COLOR=#333333],0)+[/COLOR]VLOOKUP([COLOR=#333333]J2[/COLOR][COLOR=#333333],Sheet2![/COLOR][COLOR=#333333]A:C,3[/COLOR][COLOR=#333333],0)+[/COLOR]VLOOKUP([COLOR=#333333]R6[/COLOR][COLOR=#333333],Sheet2![/COLOR][COLOR=#333333]A:C,3[/COLOR][COLOR=#333333],0)[/COLOR]

 

[squarepants]

This worked great. Thanks a million Ben.

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!