Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Making an estimate

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.


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Making an estimate

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Making an estimate

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Standard Error of the Estimate iperlovsky Excel Worksheet Functions 2 November 3rd 09 04:53 PM
Estimate time of arrival (ETA) LOSTHOUND Excel Discussion (Misc queries) 1 April 28th 08 08:50 PM
How to indicate that a value is an estimate? couture57 Excel Discussion (Misc queries) 7 March 26th 08 01:43 AM
How to set up a cost estimate spreadsheet? mgirving Excel Worksheet Functions 2 September 1st 06 03:47 PM
Template for estimate DIDI50 Excel Discussion (Misc queries) 0 January 19th 05 01:13 AM


All times are GMT +1. The time now is 09:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"