Excel 2010 Customer Price List

Posted on 2012-08-31
Medium Priority
Last Modified: 2012-08-31
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?
Question by:ITworks
  • 2
  • 2
LVL 19

Expert Comment

by:Ken Butters
ID: 38355191
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

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.

Author Comment

ID: 38355234
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.
LVL 19

Accepted Solution

Ken Butters earned 2000 total points
ID: 38355468
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.

Author Closing Comment

ID: 38355493
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!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question