Automating estimate sheet

[Ray]

Hi I estimate flooring and I need to estimate and total one room at a time for insurance companys on a single spreadsheet, each room is different and may use more lines or less lines.

I made an example of how it needs to look:

http://www.muzecraft.com/excel/testEstimate3%20.xlsx


It would be nice to somehow click a button after entering materials & labour to calculate the costs and display it like shown in the provided excel sheet. Need to somehow determine if its labour or product as well since you can't tax pst on labour


Thanks,

Ray

 

[SirJB7]

Hi, Ray!


I think there's no need of a button, with the 4 total lines below each room items it's enough. Your only issue is to determinate when an item is product or labour, and you could do it as follows:

a) add a helper column to identify item types, P or L, or just L for labour

b) use always the word "Labour" (either in lower or uppercase or capital) in the item description


Regarding on your definition it'd be different the solution.


Regards!

 

[Ray]

So I'm guessing this would be the template minus the formulas

3 questions, How would I direct the word Labour to H4 cell? next question is that with each room that Labour cell (H4) would change, there must be a formula for that? the same question would be for the Material cell D4 which would change all the time


http://www.muzecraft.com/excel/testEstimate4%20.xlsx


Thanks!

 

[SirJB7]

Hi, Ray!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Automating%20estimate%20sheet%20-%20testEstimate3%20%28for%20Ray%20at%20chandoo.org%29.xlsm


It's created from the third example of your previous uploaded file ("testEstimate3 .xlsx").

The only changes are in these formulas:

F9: =SUMAR.SI(TableData[Product];"*labour*";TableData[Total]) -----> in english: =SUMIF(TableData[Product],"*labour*",TableData[Total])

H9: SUMA(TableData[Total])-F9 -----> in english: SUM(TableData[Total])-F9


If this is suitable for you, maybe you can enhance it with some VBA code for:

a) resetting the data

b) adding and deleting rows automatically


For the time being you'd have to do this manually. I leave you this ideas for the 2nd part.


Regarding the 1st one (uploaded file), just advise if any issue.


Regards!

 

[SirJB7]

Hi, Ray!


Download again the updated file from same previous link.

Button Reset deletes all rows except 2 first (row 2 and 3) if cell at column A is blank.

Adding any text in the cell at column A for row next to table and previous to totals lines adds a new blank line to separate them. Please note that this uses a named range Totals1st for cell at column A of first totals row.


Regards!

 

[Ray]

I'm sorry I'm not fully understanding how your formula works

If I understand correctly it is based on matching text?

I have it going backwards as you can see in the file below http://www.muzecraft.com/excel/testEstimate5.xlsm


Its getting close!

Thanks!

Ray

 

[SirJB7]

Hi, Ray!


Firstly I'd like to know if it works for you as for me it performed well in my tests.


Then let us go to how it works. This is the involved code:

-----

[pre]

Option Explicit

' constants
Const gksTotals1st = "Totals1st"

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
' declarations
Dim rng As Range
Dim I As Long
' start
Set rng = Range(gksTotals1st).Offset(-1, 0)
If Application.Intersect(rng, Target) Is Nothing Then Exit Sub
If rng.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
' process
If rng.Value <> "" Then
I = rng.Row
Cells(I + 1, 1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
' end
Application.EnableEvents = True
Set rng = Nothing
End Sub

Private Sub cmdReset_Click()
' constants
' declarations
Dim I As Long
' start
Application.EnableEvents = False
' process
For I = Range(gksTotals1st).Row - 2 To 4 Step -1
With Cells(I, 1)
If .Value = "" Then .EntireRow.Delete Shift:=xlUp
End With
Next I
' end
Application.EnableEvents = True
End Sub

[/pre]
-----


This two short pieces of code work as follow:

a) Worksheet_Change event

There is a named range for cell at column A of 1st row of totals (in my uploaded file, cell A9) named "Totals1st". The code in this event is triggered whenever you enter a new value or you update an existing value in a cell. In this case it fetches for entering values in column A at previous row of Totals1st and adds a new blank line so as to keep totals separated from detail: it only inserts a blank line as the new data and formula row is added automatically by the table features.

b) Reset button

This is the final cleaning done to delete all rows in table (except the 2 first) so as to don't keep blank data rows.


The total formulas are only modified as I posted previously.


Hope this help, if not just advise. I didn't worked with your last uploaded file (testEstimate5.xlsm) as I think you'd go on working with my last uploaded file (testEstimate3.xlsm), if what I explained before it's Ok for you.


Regards!

 

[Ray]

Hi, I'm altering the labour rates in the example you uploaded but it doesn't change the value of the labour, the labour cell is calculating the material as well?


Thanks

Ray

 

[Ray]

Meanwhile I'll try to interpid your coding

 

[SirJB7]

Hi, Ray!

I didn't make any change at all in your total formulas. Despite of this, I noticed that:

- you apply a 7% to Material, as PST

- you apply a 7% to Material + PST + Labour, as GST (so you're applying twice 7% to Material)

It sounded strange to me but I don't know your local tax regulations. If it isn't what you wanted to mean, please elaborate and write down an example with all items tied to the all different tax types possible.

Regards!