Solved

# Excel 2010 Customer Price List

Posted on 2012-08-31
504 Views
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
Question by:ITworks

LVL 19

Expert Comment

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

LVL 4

Author Comment

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

LVL 19

Accepted Solution

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

LVL 4

Author Closing Comment

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

## Featured Post

### Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.