Making an estimate

[PJ Burke]

I have Excel 2010 and i am trying to make an estimating program to coorespond with our products. I have been using a template that was provided by Excel 2010 and trying to incorporate our estimating program that we have had on Excel for a few years.
The problem I am having is with the description line on the estimate template. I have it set up so i can fill out a questionaire and after answering the questions then i have a static list of what i want in the description line and a value in the cell next to it that will change if that item is to be included in the estimate.
The chart looks like this:

A B

UT 4542.461
TT Rec 0
Hydraulic Motor 0
Vinyl Recessed Housing 852.5
GRO4000 Undertrack Retainers 350
GRO4100 Undertrack Retainers 0
DDO4000 Double Deck Retainers 0
Vinyl Liner Kit 0
Vertical Flush Mounted Tracks 0
Horizontal Flush Mounted Tracks 0
Top Track 0
Aluminum Lid 0
Adjustable Walk on Lid Brackets 1615
Installation of Brackets 275
Installation 1400


These are two different columns. In Column A the words are static and the values(column B) will change based on the questions that are answered. Basically I want everything that has a value >0 in column B to be on the estimate. I want the description line to show the words in column A that coorespond with column B without any spaces in between them. Different estimates will have a different number of line items so i want to only include the line items in the estimate that will be included in the quote.
I hope i am explaining it correctly. Let me know if you need more information. Thanks.

 

[GS]

PJ Burke explained on 3/24/2011 :

I have Excel 2010 and i am trying to make an estimating program to coorespond
with our products. I have been using a template that was provided by Excel
2010 and trying to incorporate our estimating program that we have had on
Excel for a few years. The problem I am having is with the description line
on the estimate template. I have it set up so i can fill out a questionaire
and after answering the questions then i have a static list of what i want in
the description line and a value in the cell next to it that will change if
that item is to be included in the estimate. The chart looks like this:

A B

UT 4542.461
TT Rec 0
Hydraulic Motor 0
Vinyl Recessed Housing 852.5
GRO4000 Undertrack Retainers 350
GRO4100 Undertrack Retainers 0
DDO4000 Double Deck Retainers 0
Vinyl Liner Kit 0
Vertical Flush Mounted Tracks 0
Horizontal Flush Mounted Tracks 0
Top Track 0
Aluminum Lid 0
Adjustable Walk on Lid Brackets 1615
Installation of Brackets 275
Installation 1400


These are two different columns. In Column A the words are static and the
values(column B) will change based on the questions that are answered.
Basically I want everything that has a value >0 in column B to be on the
estimate. I want the description line to show the words in column A that
coorespond with column B without any spaces in between them. Different
estimates will have a different number of line items so i want to only
include the line items in the estimate that will be included in the quote. I
hope i am explaining it correctly. Let me know if you need more information.
Thanks.

I did something similar for a machinery supplier where they had a
different template for each piece of machinery (18) they sold, and each
template had all posible options for its respective piece of equipment.
The template structure was designed as follows:

B:B; Qty
C:H; Description (in various configurations)
I:I; Unit Price
J:J; Amount

B?:J?; Print_Area

Salesreps would enter a qty and the amount would automatically
calculate. A 'flag' ("P") was set in K:K (hidden col) so that only
selected items would print. The app printed Quotations, Sales Orders
(with signature lines) for reps to use with clients, and Purchase
Orders for reps to use with ordering from the equipment supplier.

Any line that did not have a qty entered had its entire row hidden.
Changing from Quotes to SOs to POs was a simple menu selection that
updated a named range.

The Qty col was formatted to display whole numbers only so discounts
could be passed on. For example...

Normal entry:
Qty=1, Price=$100.00, Amount=$100.00

Discounted entry:
Qty=0.9 (displays '1'), Price=$100.00, Amount=$90.00

Autofilter was applied to colK to toggle the display of selected items
only on/off via a menuitem.

The headings row (Qty, Description, Unit Price, Amount) was repeated
for multi-page printouts.

HTH

 

[GS]

I forgot to include the Part No col in my structure example:

B:B; Qty
C:E; Part No
F:H; Description (in various configurations)
I:I; Unit Price
J:J; Amount

This was later revised as follows:

B:B; Qty
C:E; Part No
F:J; Description1
F:G; Description1S, H:J; Description2
F:H; Description1L, I:J; Description2
K:K; Unit Price
L:L; Amount
M:M; formula to set print flag
B?:L?; Print_Area

All templates were built using a master template that contained all the
business logic, needing only the line items to be inserted as required.