Writing and Calculating Quotes

[SKAHLE]

I am trying to use Excel to build quotes for customers. I did this a long time ago in Quatro Pro, but I am having difficulty doing it I Excel.

I am a home builder and have various standard models of homes.

• When I do quotes, I list all the specifications included.
• I have a worksheet with a table listing all the specifications available. Each line (record) contains columns listing the description and formulas for calculating the quantity and costs. I print out the first few columns as a PDF for the quote presented to the customer.
• I need to pull the appropriate lines from the data table into my new quote worksheet that pertain to the particular model that I am quoting. From there, I begin to customize it, but that is the nest step I will address after I get step one figured out.

I accomplished this before by having a column for each model and a simple ‘X’ in the lines that are appropriate. It was a long time ago, but I think I had a dialog box choose the model and a macro that would go find all the appropriate lines for the particular model, copy them and paste into the new quote worksheet. All the formulas would remain to calculate properly.

In Excel, I tried using an advanced filer, but it only returns values and not formulas. Also, I have to go through too many steps each time: Data>Advanced Filter>Select Copy to Another Locaction, List Range, Criteria Range and Copy to. It is too cumbersome and time consuming and easier to have errors by selecting the wrong range.

Is there a way to do this in Excel?

Thanks in advance for any help.

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[SKAHLE]

Here are two sample files; one for the data table and one for the new quote. I want to keep them separate because the data such as prices need to be updated from time to time and want just one file to update.

If you got to the "House_Quote_SAMPLE" worksheet, you will see a drop down list at cell A1. You can choose one and then run "Advanced Filter" and use:

List Range: Specifications_SAMPLE.xlsx!StdSpecs
Criteria Range: Sheet1!Criteria
Copt To: Sheet1!$B$1:$K$1

You will see all the appropriate specs fill in for that model.

This is NOT the best way to do it because:
• It is too complicated with too many steps involved and too many things to specify each time
• Formulas are lost
• The drop down list needs the list to exist in the current file rather than in the Specifications file. The drop down list text is too small anyway.
•

I hope this is understandable. It is difficult for me to explain this in writing.

[JeteMc]

I would suggest keeping the specifications and quote on different sheets rather than in separate workbooks.
Note that in the attached file the specifications are on Sheet3 which has been hidden for aesthetics/privacy.
A helper column (M) is added to Sheet3 and is populated using: =IF(COUNTA(A4:B4)=2,"Both",INDEX(A$1:B$1,MATCH("X",A4:B4,0)))
On Sheet1 cell C2 is populated using: ="Base Model: "&A1
Cells E2:K2 are populated using: =INDEX(Sheet3!E4:E5,MATCH($A1,Sheet3!$M4:$M5,0))
Cells B4:K28 are populated using: =IFERROR(INDEX(Sheet3!C$7:C$31,AGGREGATE(15,6,(ROW($C$7:$C$31)-ROW($C$6))/((Sheet3!$M$7:$M$31=$A$1)+(Sheet3!$M$7:$M$31="Both")),ROWS($A$1:$A1))),"")
The range B2:K28 has conditional formatting applied to hide zero values: Cell Value = 0 (font set to white)
Let us know if you have any questions.