• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

Excel 2010 Customer Price List

I am trying to make an Excel sheet that my customers can enter a few pieces of information and get their cost for a product.  In Sheet2, I have a tool I use to calculate the price of some Studs.  Because the price varies depending on the diameter of the material, I have a template for each size stud (1/2", 5/8", 3/4", etc.)  I enter the Quantity and the Length and then the price is calculated automatically.  I don't want my customer to see Sheet 2.  Instead, I want to figure out a way for the customer to use something like what is on Sheet 1, where all they have to enter is the Quantity, Diameter and Length.  I want the Diameter to be based on a list, which I know how to do.  What I can't figure out is how after the Length is entered to get it to calculate the price.
EE-Example.xlsx
0
ITworks
Asked:
ITworks
  • 6
  • 3
  • 2
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
Looks like you could achieve what you want using a HLOOKUP formula, using the data on sheet2 as the lookup table.

Some examples would be good.

Thanks
Rob H
0
 
ITworksAuthor Commented:
An exampled could be made by going to sheet 2 and entering in a quanity of 10 in cell B3, and 6.50 in cell B4.  This would be what I would want the customer to enter in Sheet 1 in cells B21 (Quantity), Cell B2 (drop down list to 1/2-13) and cell B3 enter 6.5.  Then the price of $1.10 should populate in Sheet 1, Cell B4

       1/2" A/T STUD      
Quantity      10
Length      6.5
Weight ea.      0.29
Cost per FT      $1.00
      
Price ea.      $1.10
0
 
SteveCommented:
Attached is an EXTREMELY ROUGH version of what you want using userform.

Is this something that would be nearer your requirement?
The data is yet to be fully sorted, but it is just to give you idea.
If you like this, I will program it a bit better, and so it is easy to update and use.

So if you like the idea, can work from here:
Uform-Example.xlsm
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ITworksAuthor Commented:
That's EXACTLY what I need!     How do I do it?
0
 
SteveCommented:
OK, I shall complete the file for you in a format I would call "finished".
Then can explain how the finished article works.
0
 
Rob HensonIT & Database AssistantCommented:
See attached.

The Quantity & Length inputs from sheet1 feed into the calculations on sheet2 and then the Price & Weight are fed back to the Input using a hlookup on the Diameter.

On sheet2 I have moved the Diameters across one column to be over the calc columns and got rid of the surplus spaces.

Thanks
Rob H
Price-Calculation.xls
0
 
SteveCommented:
OK, attached if the UserForm method for your data.
If you could test it and let me know if it is OK.
The password is "The_Barman" (case sensitive)
If it runs OK, can go over the code and how it works.
Otherwise, can do fixes and then explain.
PriceWorkbook.xlsm
0
 
SteveCommented:
One thing that worries me is that the price does not change dependent on Quantity.
I would expect there to be a change in piece price as the quantity increases.

Is the piece price to go down as quantity increases?

For a price such as these I would expect to see a "fixed" and "variable" portion of the cost.

Such as:
(Setting + (Run x quantity)) x rate
this would give the result of a decreasing price as quantity increases
0
 
SteveCommented:
Attached is the workbook I have posted earlier, but with a small change to allow for Setting and Running costs to be allowed for.
This gives a more variable cost based upon quantity.
The values in the data sheet may need changing to more reasonable values as I can only guess at reasonable figures.
This may not be what you are looking for, but I thought it may be interesting to you.
PriceWorkbook.xlsm
0
 
ITworksAuthor Commented:
On other product, I do have a decrease in price based on quantity ordered but this one is an exception.  I will review your PriceWorkbook for future reference.

So far, everyone that I have checked on your spreadsheet is working correctly.
0
 
SteveCommented:
OK, will take one step back to the version without the Set/Run additions:

The data Tab holds all the variable information. This is called from the macro and used to generate the costs.
There are two Sheet Hide buttons which use code to set the visible properties of the Data sheet from Visible to VeryHidden.
To see the code for the form press [Alt]+[F11] this will open the VBA editor.
In the editor you will see a white area with "VBAPoroject (PriceWorkbook.xlsm)" if you double click that and enter the password as before the objects folders will be visible.
If you look in the Forms Folder you will see an object PCalc this is the form which does the calculations. you can right click it and either view object or view code
View object allows for the design of the form.
Vew Code will open the code. Code starting with an apostrophe is note text and not part of the running. I have used this to give the attached file details on what is happening.

If you have a look and ask questions about any areas that are not clear.
PriceWorkbook.xlsm
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now