Creating a quoting template

[jol]

I have been asked to create a template used for quoting multiple similar items (furniture), that will ultimately create a price for all items and print out a record for clients.

I have created the basic worksheet that calculates the price of materials for a single item, but the question is here, what is the next best step?

The initial brief was to have a worksheet that copied one pricing template to the next line below on the same worksheet and continued on for however many new copies of the template were required untill the entire quote of furniture pieces was finished, and then copy the basic details of every item (job#, description, final price) to a new 'quote' worksheet, and then to calculate a final price and printout.

This goes somewhat beyond my capabilites and the solution to this could be to have the initial template - once each item of furniture is created, to copy and paste a few named ranges to the 'quote' sheet and then calculate that once it's all finished.
The only problem I can see with this method is that, is that it doesn't make it so user friendly to go back an edit the quote if a mistake on a single item is made.

I would be interested to learn any opinions and perhaps examples if anyone knows of any.

 

[SamTYler]

Sheets Required:

1. Input
   • Used to record
     1. customer info
     2. Job info
     3. Items ordered
2. Items Offered
   • A Database Table
3. Price Lists
   • A Database Table
4. Calculators
   • Holds all formulas
5. Output
   • The Quote Sheet
6. Output Headers and Footers
   • Intermediate headers and footers
   • Last page footer

Layouts:

1. Input
   1. Top Few Rows
      • Customer and job Info
   2. Items Ordered
      • Enough lines to hold the maximum number of items expected to be ordered.
2. Calculator
   • Repeat the basic worksheet that calculates for a single item enough times to calculate the maximum number of items expected to be ordered.
3. OutPut
   • Header
     1. Customer and job info
   • Enough lines to hold the maximum number of items expected to be ordered
   • Sum Totals, taxes, etc.

Named Ranges:

1. All input ranges
   • Name Item cells
     1. Item number as suffix
2. All Calculator Results
   • Item number as suffix
3. Database Columns same as Headers
   • Change spaces to underscores
   • Prefix with with three letter code
     1. Items Offered sheet
     2. Price sheet

Output formulas:

1. Headers
   • =Input Header Names
2. Items
   • =If(InputItemNumberName = "","",InputItemNumberName)

You can also see my "Under Construction" Website for more ideas.

MS Excel for Contractors

I probably won't be here later, but someone else will help you as you progress thru this work.

 

[jol]

Thank you for your help and suggestions Sam. Certainly a huge help and a big step in the right direction.
I look forward to seeing your tutorials on online soon.

Hopefully I won't have too many problems as it all moves along