# Creating Custom Product SKU and Pricing

#### squarepants

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?

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.

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
Code:
``=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
Code:
``=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]``

This worked great. Thanks a million Ben.

No problem Glad I could help Appreciate the feedback!

Replies
1
Views
236
Replies
9
Views
247
Replies
1
Views
762
Replies
10
Views
511
Replies
1
Views
1K

1,207,258
Messages
6,077,345
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back