Invoice with percent complete

[tb]

I am looking for a template for Invoices to calculate for contracts. I looked
through templates and cannot find anything to work, any other suggestions?

I have my basic Invoice, which works good, but when it comes to contract
Jobs I am trying to find a template to enter in 'Contract Amount' and % we
have completed to date to get total due. When I go to Invoice again I want to
try and have it where it comes up 'Less Previous billed' to calculate my new
Invoice owing.

Thank you again......I really appreciate all the help!!!

 

[Billy Liddel]

TB This is a simplified solution. Try it in a new book. if you are using
xl2007 save it as a macro enabled workbook.

Contract no: A1234 Previous Billed 4400
Contract amount: 10000
% delivered 56%
Amount Owing 5600

Current Invoice Amount 700

You need a macro to add the current invoice amount to the value of the
previous amount. This is done before save which can be called by priting the
invoice.

The macros are:

==============
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rngPreviouslyBilled As Range
Dim rngContractAmount As Range
Dim rngInvoiceTotal As Range

Set rngPreviouslyBilled = Range("H1") 'Change to suit
Set rngContractAmount = Range("E2") 'Change to suit
Set rngInvoiceTotal = Range("E6") 'Change to suit

rngPreviouslyBilled = rngPreviouslyBilled + rngInvoiceTotal

End Sub

==============

The are placed in the workbook module. Press ALT + F11, ALT + R, to open the
project explorer. Right-click the ThisWorkbook icon and choose View Code,
paste in the code and return to the workbook

Press Ctrl P to activate the routine.

Contract amount is in E2
Previously Billed is in H2
Formula for % Billed (in E3) is =(E2-H1)/E2
Formula for amount owing in E4) is = E2-H1

In actual fact when there are more than one contract involved you will need
a Table of contract numbers, the contracts amount and the Previously billed
amount.
This list must be sored in contract number order and use VLOOKUP formula to
get the amount. the code to add the current invoice amount to the list will
be more complicated. But this can help get you started.

HTH
Peter Atherton

 

[Billy Liddel]

Here is a macro to update the list with the amount on the invoice. The list
has a dynamic range name for the contract numbers (in ascending order) and
Contract details that includes the the Contract ID, the contract amount, the
amount previously billed, and the customer ID.

Each time the macro is run the Invoice total is added to the contract details.

I've included a msgbox so that you can check.

Public Sub UpdateContractsList()
Dim rngOrders As Range
Dim rngContractID As Range 'Dynamic range name
Dim rngInvoiceTotal As Range
Dim rngAmoutLeft As Range
Dim rngAmountLeft As Range
Dim rngOrderDetails As Range
Dim x
Dim lRow As Long

Set rngOrderDetails = Range("Contracts")
Set rngContractID = Sheets("Sheet1").Range("E1")
Set rngAmoutLeft = Sheets("Sheet1").Range("E1")
Set rngInvoiceTotal = Sheets("Sheet1").Range("E6")
Set rngOrders = Range("ContractNumbers")
'how much do we owe?
x = WorksheetFunction.VLookup(rngContractID.Value, rngOrderDetails, 3, 0)
MsgBox x
lRow = WorksheetFunction.Match(rngContractID.Value, rngOrders, 0) + 1
'add Invoice total to amount paid
With Sheets("Sheet2")
.Cells(lRow, 3) = .Cells(lRow, 3).Value + rngInvoiceTotal.Value
End With
'now how much is paid?
x = WorksheetFunction.VLookup(rngContractID.Value, rngOrderDetails, 3, 0)
MsgBox x
End Sub

If you would me to look at it further yo can send me your file, together
with details of exactly you want.

peter_atherton at hotmail.com

change the at in the address as normal.

Peter