Prev: New to VLOOKUP ...merge addresses?
Next: function to populate a cell where formula is NOT located
From: Jim on 4 Apr 2010 04:25
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
From: Bob Phillips on 4 Apr 2010 05:37

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


--

HTH

Bob

"Jim" <Jim(a)discussions.microsoft.com> wrote in message
news:1F391262-8AC0-4515-9779-CBEF6906F399(a)microsoft.com...
>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


From: Jim on 4 Apr 2010 07:03
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" wrote:

>
> How about putting them at the top of the table, they will never need to move
> then.
>
>
> --
>
> HTH
>
> Bob
>
> "Jim" <Jim(a)discussions.microsoft.com> wrote in message
> news:1F391262-8AC0-4515-9779-CBEF6906F399(a)microsoft.com...
> >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
>
>
> .
>
From: Bob Phillips on 4 Apr 2010 07:33
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.

--

HTH

Bob

"Jim" <Jim(a)discussions.microsoft.com> wrote in message
news:9A1711DC-C91D-4AA4-A7DC-19B454E4B6ED(a)microsoft.com...
>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" wrote:
>
>>
>> How about putting them at the top of the table, they will never need to
>> move
>> then.
>>
>>
>> --
>>
>> HTH
>>
>> Bob
>>
>> "Jim" <Jim(a)discussions.microsoft.com> wrote in message
>> news:1F391262-8AC0-4515-9779-CBEF6906F399(a)microsoft.com...
>> >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
>>
>>
>> .
>>


From: Project Mangler on 4 Apr 2010 08:24
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

"Bob Phillips" <bob.phillips(a)somewhere.com> wrote in message
news:u$V76q%230KHA.3652(a)TK2MSFTNGP04.phx.gbl...
> 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.
>
> --
>
> HTH
>
> Bob
>
> "Jim" <Jim(a)discussions.microsoft.com> wrote in message
> news:9A1711DC-C91D-4AA4-A7DC-19B454E4B6ED(a)microsoft.com...
> >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" wrote:
> >
> >>
> >> How about putting them at the top of the table, they will never need to
> >> move
> >> then.
> >>
> >>
> >> --
> >>
> >> HTH
> >>
> >> Bob
> >>
> >> "Jim" <Jim(a)discussions.microsoft.com> wrote in message
> >> news:1F391262-8AC0-4515-9779-CBEF6906F399(a)microsoft.com...
> >> >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
> >>
> >>
> >> .
> >>
>
>


 |  Next  |  Last
Pages: 1 2
Prev: New to VLOOKUP ...merge addresses?
Next: function to populate a cell where formula is NOT located