• Status: Solved
• Priority: Medium
• Security: Public
• Views: 555

# Excel 2010 Customer Price List

I have a spreadsheet that I use to calculate the cost of a product.  It was originally designed to be used for one type of material, that's called B7.  But I would like to use it to calculate other materials as well, (SS,Low Carbon)  Currently if you go to the tab called Stud Price Calculator and enter in the quantity, size (drop-down list) and length, it will calculate the price and weight.  It does this in Sheet!Calculation.    I guess I would need to add a drop down box in the first tab and have the user select which material, that I understand.  What I'm having trouble figuring out is how do I tell it to calculate based on the prices for the different material?
Stud-Pricing-Calculator.xls
0
ITworks
• 2
• 2
1 Solution

Commented:
This spreadsheet is using some named ranges to get started.  To see the named ranges, go to the Formulas tab, and click on the icon for "Name Manager".

When you do this you should most of the named ranges are on the Stud Price Calculator sheet.

Names there are :
Quantity Cell C2
Diameter --- cell C3
Length --- Cell C4
Price --- Cell C5
Weight -- Cell C6

The only other named range is on Sheet B7.   That named range is called Diameters.
Diameters is defined as Cells A3 through A29 on Sheet B7.  From what I can tell this is only used to populate the dropdown on Stud Price Calculator.

Cells C5 and C6 on the Stud Price Calulator both do a lookup on the Calculation page.
Example : C5

=IF(OR(Diameter="",Diameter=0,Quantity="",Quantity=0),0,HLOOKUP(Diameter,Calculation!\$C\$1:\$AA\$10,10,FALSE))

This says... if the  diameter is not zero or blank, and the quantity is not zero or blank... then do a lookup of my Price per Piece based on the diameter.

The hlookup will find the column with the matching diamater in the top row of Calcuation... and then return the value in the same column  ... from Row 10.... Stock w/Markup\$

Bottom line... if you want to get this to work with other products.... in a similar fashion... you will need to do the following.

1) update dropdown list in C3 (stud price calulator sheet) to reflect diameters for other products.
2) create a different Calculation sheet for each product.
3) update the formulas in cells C5 and C6 to reflect the new calculation sheet you created in step 2.

Instead of unnecessarily complicating things... I would recommend a different price calulator sheet for each product as well.
0

Author Commented:
Ok, I sort of get it...but....I may have the same diameters for both B7, Low Carbon and SS.   So that's why I made a tab called All Material.  It shows the price and weight for everything.  I was hoping I could somehow but another drop down on the Stud Price Calculator to choose which material, then let the user choose the diameter because I don't know how to differentiate from a 5/8-11 B7, 5/8-11 304SS, 5/8-11 316SS, or 5/8-11 Low Carbon.
0

Commented:
You will have to check the calculations to see if they are right... but I updated the attached spreadsheet to show you another way to do this....

I add a new sheet... Calculation Low Carbon.

Added new named ranges... for Low Carbon

Updated the calculation sheet for low carbon to do a vlookup instead of hard coding the values.

There could be some errors in the calc... but it should work.  At least give you an idea of one way to solve it.
Copy-of-Stud-Pricing-Calculator.xls
0

Author Commented:
Got it, Thanks!  I never thought of doing it that way.  I was hung up on the user having to select the material grade first.

Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.