Dynamic Table Problem

[Jim]

I am using excel 2007.

I want to create an invoice where the total number of line items is dynamic.
I have created the 'header' section of the invoice with company name,
customer name, etc and I have created the 'body' of the invoice using a
table. This table has Qty, Description, Unit Cost, and Item Cost. Item cost
is a formula showing Qty * Unit Cost. This table works great and is dynamic.
As I simply type more items, the table automatically expands and formats
correctly.

The trouble I have is I want to have a Subtotal, Tax, and Grand Total line
shown at the bottom of the table and to move as it expands or shrinks. How
can I make these three cells move with the table?

Thank you,

JIM

 

[Bob Phillips]

How about putting them at the top of the table, they will never need to move
then.

 

[Jim]

I had thought of that but traditional invoices have the total at the 'Bottom
Line'. If there is no solution to this problem, I will probably make the
body of the invoice NOT a table, and then just have to copy/cut /paste to
expand or shrink the invoice.

Hopefully there is a solution.

JIM

 

[Bob Phillips]

They do, but live on the edge!

Another easy solution is to have a fixed layout, say 20 lines for items, and
then have a totals line after that, so again it doesn't move.

If you want the totals line after the last item, you either need to always
insert a new items line and have a totals formula something like

=SUM(I3:OFFSET(I11,-1,0))

where the totals cell is I11, or use VBA.

 

[Project Mangler]

Hi Jim,

I don't have Excel2007 handy and I'm not familiar with the use of tables.

The following works in Excel 2003 and maintains a one row gap between the
last "invoice row" and the Subtotal row with a subtotal formula in ColD.

Assumptions are:
Col A header "Quantity"
Col B header "Description"
Col C header "Unit Cost"
Col D header "Item Cost"
On the Subtotal Row "Subtotal" is in Col A, the formula is in Col D
On the Tax row "Tax" is in Col A , formula in Col D
Same layout on the Grand Total Row.

Right click on the worksheet tab and select "view code". Paste the code
below in there.

Change the words "Quantity" and "Subtotal" in the code below to match what
you call these items.

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False

Dim LastRow As Long
Dim SubTotRow As Long
Dim CurRow As Long
Dim LastRecord As Long
Dim QtyRow As Long

CurRow = ActiveCell.Row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
SubTotRow = Range("A1", "A" & LastRow).Find(what:="Subtotal",
lookat:=xlWhole).Row
QtyRow = Range("A1", "A" & LastRow).Find(what:="Quantity",
lookat:=xlWhole).Row
LastRecord = Range("A" & SubTotRow).End(xlUp).Row
If LastRecord < QtyRow + 1 Then LastRecord = SubTotRow - 1


If (SubTotRow - LastRecord) = 1 Then
Cells(SubTotRow, 1).EntireRow.Insert
SubTotRow=SubTotRow+1
Else
On Error Resume Next
Range("A" & QtyRow, "A" & SubTotRow -
2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
SubTotRow=SubTotRow-1
End If
Range("D" & SubTotRow) = "=SUM(D" & QtyRow & ": D" & SubTotRow - 1 & ")"
Application.EnableEvents = True
End Sub

 

[Roger Govier]

Hi Jim

Ensure your table has a Total Row

In the row below the Total row, enter a space in column C and a space in
column D. This will ensure that you keep one blank row below your data
and the row with the tax amount.

2 rows under the Total row enter Tax in column C and the appropriate
formula for tax, being the Total value in column D * tax rate

2 rows below the Word tax, enter Grand Total in column C and the
relevant formula in column V

As you create more rows in the body of your table, the Tax and Grand
Total rows will move down accordingly (or up, if you delete rows from
the table.