Totaling amounts spread over multiple sheets

[Phillip Jones, C.E.T.]

As a result a fall sustained in a hospital, my mother 84 broke a Rib and
is now in Rehab in a Nursing home.

As a result I've had to take over her bills for a few months.

I have set up four sheets in a workbook (using Excel2008)

She has some bills that reoccur and some unique to particular months.
Normal because I live with her I pay room and Board despite helping
around the house, taking to doctor's appointments, Groc store and such.

I am subtract that as an offset against her bills each month.
and when she gets back she wants to repay me the difference.

I have bills paid in one column and my R&B in another column and
subtract one from the other. (actually I am using accounting format and
entering my R&B as a Positive and her bills as a negative and adding the
total of the bills to my R&B which ends up a negative number.

how do I create a Grand total at the last page that covers all the pages?

 

[Phillip Jones, C.E.T.]

Let's see if I can do this better:

As a result a fall sustained in a hospital, my mother 84 broke a Rib and
is now in Rehab in a Nursing home.

As a result I've had to take over her bills for a few months.

I have set up four sheets in a workbook (using Excel2008).

She has some bills that reoccur, and some unique to particular months.
*Normally,* because I live with her, I pay room and Board; despite
helping around the house, taking to doctor's appointments, Groc store
and such.

I am *subtracting* that as an offset against her bills each month.

When she gets back she wants to repay me the difference.

I have bills paid in one column and my R&B in another column and
subtract one from the other. *(Actually I am using accounting format and
entering my R&B as a Positive and her bills as a negative and adding the
total of the bills to my R&B which ends up a negative number)*.

how do I create a Grand total at the last page that covers all the
pages? *And how can I get then to dynamically change each time I enter a
new item*?

 

[CyberTaz]

Hi Phillip;

There are at least 3 options:

#1- In the Grand Total cell type an = then click the tab of the first sheet,
click its Total cell. Type a + then click the tab of the next sheet, click
its total cell, etc. The result will be something like;

=Sheet1!D25+Sheet2!B27+Sheet3!J30

#2- Just put a SUM() function in the cell on your last sheet where you want
the Grand Total to appear. Enter the arguments of the function as follows:

Click the tab of the first sheet, click the cell containing its Total.

Then type a comma & repeat as above for each sheet whose Total you want
included in the Grand Total. The result will be something like;

=SUM(Sheet1!D25,Sheet2!B27,Sheet3!J30)

#3- If the Total sheets are consecutive sheets in the book & the Total is in
the same respective cell on each of the sheets you can also use the SUM()
function but to enter the arguments:

Click the tab of the first sheet, click the Total cell, then Shift-Click the
tab of the last sheet & press return. The result will be something like;

=SUM(Sheet1:Sheet3!D25)


Either method will produce a Grand Total which will update based on changes
made in the source sheets.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Laroche J]

If I understand Phillip's question correctly, the number of items is
different on each sheet AND changes continuously. Hence the trouble in
getting a Grand total that includes all items on all sheets.

How about putting each sheet's total at the top, where it would be in a
fixed location, rather than at the bottom? Bob's formula would then work all
the time, but with this format:
=SUM(Sheet1:Sheet3!D1)

It means that, in this example, each sheet's D1 would have a formula such
as:
=SUM(D3300)

Another alternative is to name the cells that include the total of each
sheet. The Grand total would then be:
=sum1+sum2+sum3
Even though those cells would move Excel would always take care of
maintaining the proper reference for the Grand total.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.7



CyberTaz wrote on 2009-06-07 08:06:

..... etc.

 

[CyberTaz]

Hi JL;

Valid considerations, but my interpretation of Phillip's description was
that he had already established the number of rows being used in the
structure of his source sheets so that wasn't expected to change. Even if he
were to Insert Rows/Cells on any of the source sheets, however, the formula
on the Grand Total sheet would still continue to update properly using any
of the methods involved as long as Absolute References weren't used.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Phillip Jones, C.E.T.]

Thanks! This is formula I had to come up with.

=(Sheet1!F18+Sheet2!F11+Sheet3!F10+Sheet4!F9)

Sheet one was May
Sheet two is June
Sheet three is July
Sheet 4 is August

I'm hoping she will be out before August.

If I had to insert a bill on any given page would that work?

I tried with commas said error in formula and had !NAME in the cell
Until I added the () it wouldn't work. I tried adding a Dollar to a Bill
and then saving change and yes it updated. I don't anticipating adding.
I might have to take away one, depending upon when I have to pay a
certain bill. (car insurance)

 

[Phillip Jones, C.E.T.]

Update I added one extra Bill which I had put down to all four sheets
and The formula updated perfectly.

Thanks again Tax and JL (or LJ). Got what I needed.

 

[CyberTaz]

Hi Phillip;

The first suggested method is a simple addition formula which does *not*
require the parens, although they certainly don't hurt. However, the other 2
methods employ the SUM() function which requires the inclusion of everything
beginning with = exactly as I wrote the examples in my reply ‹ the only
difference being the actual sheet & cell references for the function to use.

If you have time to play a bit this should provide the same results if you
enter it (or copy/paste) exactly as shown here. It uses the same sheet/cell
references as what you wrote below. Put this in place of your current
formula on your Grand Total sheet [substitute the Sheet1, etc. names with
the actual sheet names if necessary]:

=SUM(Sheet1:F18,Sheet2:F11,Sheet3:F10,Sheet4:F9)

Glad you got it working either way

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Phillip Jones, C.E.T.]

I tried and would work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

Hi Phillip;

The first suggested method is a simple addition formula which does *not*
require the parens, although they certainly don't hurt. However, the other 2
methods employ the SUM() function which requires the inclusion of everything
beginning with = exactly as I wrote the examples in my reply � the only
difference being the actual sheet & cell references for the function to use.

If you have time to play a bit this should provide the same results if you
enter it (or copy/paste) exactly as shown here. It uses the same sheet/cell
references as what you wrote below. Put this in place of your current
formula on your Grand Total sheet [substitute the Sheet1, etc. names with
the actual sheet names if necessary]:

=SUM(Sheet1:F18,Sheet2:F11,Sheet3:F10,Sheet4:F9)

Glad you got it working either way

Regards |:>)
Bob Jones
[MVP] Office:Mac

Thanks! This is formula I had to come up with.

=(Sheet1!F18+Sheet2!F11+Sheet3!F10+Sheet4!F9)

Sheet one was May
Sheet two is June
Sheet three is July
Sheet 4 is August

I'm hoping she will be out before August.

If I had to insert a bill on any given page would that work?

I tried with commas said error in formula and had !NAME in the cell
Until I added the () it wouldn't work. I tried adding a Dollar to a Bill
and then saving change and yes it updated. I don't anticipating adding.
I might have to take away one, depending upon when I have to pay a
certain bill. (car insurance)

 

[Phillip Jones, C.E.T.]

Missed a word:

I tried and would *not* work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

I tried and would work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

Hi Phillip;

The first suggested method is a simple addition formula which does *not*
require the parens, although they certainly don't hurt. However, the
other 2
methods employ the SUM() function which requires the inclusion of
everything
beginning with = exactly as I wrote the examples in my reply � the only
difference being the actual sheet & cell references for the function
to use.

If you have time to play a bit this should provide the same results if
you
enter it (or copy/paste) exactly as shown here. It uses the same
sheet/cell
references as what you wrote below. Put this in place of your current
formula on your Grand Total sheet [substitute the Sheet1, etc. names with
the actual sheet names if necessary]:

=SUM(Sheet1:F18,Sheet2:F11,Sheet3:F10,Sheet4:F9)

Glad you got it working either way

Regards |:>)
Bob Jones [MVP] Office:Mac

Thanks! This is formula I had to come up with.

=(Sheet1!F18+Sheet2!F11+Sheet3!F10+Sheet4!F9)

Sheet one was May
Sheet two is June
Sheet three is July
Sheet 4 is August

I'm hoping she will be out before August.

If I had to insert a bill on any given page would that work?

I tried with commas said error in formula and had !NAME in the cell
Until I added the () it wouldn't work. I tried adding a Dollar to a Bill
and then saving change and yes it updated. I don't anticipating adding.
I might have to take away one, depending upon when I have to pay a
certain bill. (car insurance)

CyberTaz wrote:
Hi JL;

Valid considerations, but my interpretation of Phillip's description
was
that he had already established the number of rows being used in the
structure of his source sheets so that wasn't expected to change.
Even if he
were to Insert Rows/Cells on any of the source sheets, however, the
formula
on the Grand Total sheet would still continue to update properly
using any
of the methods involved as long as Absolute References weren't used.

Regards |:>)
Bob Jones [MVP] Office:Mac



On 6/7/09 10:20 AM, in article C65144FF.CE62%[email protected],

If I understand Phillip's question correctly, the number of items is
different on each sheet AND changes continuously. Hence the trouble in
getting a Grand total that includes all items on all sheets.

How about putting each sheet's total at the top, where it would be
in a
fixed location, rather than at the bottom? Bob's formula would then
work all
the time, but with this format:
=SUM(Sheet1:Sheet3!D1)

It means that, in this example, each sheet's D1 would have a
formula such
as:
=SUM(D3300)

Another alternative is to name the cells that include the total of
each
sheet. The Grand total would then be:
=sum1+sum2+sum3
Even though those cells would move Excel would always take care of
maintaining the proper reference for the Grand total.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.7



CyberTaz wrote on 2009-06-07 08:06:

.... etc.

=SUM(Sheet1:Sheet3!D25)

Phillip Jones, C.E.T. wrote:

how do I create a Grand total at the last page that covers all the
pages? *And how can I get then to dynamically change each time I
enter a
new item*?

 

[CyberTaz]

Yeah, that was boo-boo on my part 8>} I think I was trying to do too many
things at once... But at least I had it right the first time.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Missed a word:

I tried and would *not* work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

I tried and would work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

Hi Phillip;

The first suggested method is a simple addition formula which does *not*
require the parens, although they certainly don't hurt. However, the
other 2
methods employ the SUM() function which requires the inclusion of
everything
beginning with = exactly as I wrote the examples in my reply � the only
difference being the actual sheet & cell references for the function
to use.

If you have time to play a bit this should provide the same results if
you
enter it (or copy/paste) exactly as shown here. It uses the same
sheet/cell
references as what you wrote below. Put this in place of your current
formula on your Grand Total sheet [substitute the Sheet1, etc. names with
the actual sheet names if necessary]:

=SUM(Sheet1:F18,Sheet2:F11,Sheet3:F10,Sheet4:F9)

Glad you got it working either way

Regards |:>)
Bob Jones [MVP] Office:Mac



On 6/7/09 2:44 PM, in article (e-mail address removed),

Thanks! This is formula I had to come up with.

=(Sheet1!F18+Sheet2!F11+Sheet3!F10+Sheet4!F9)

Sheet one was May
Sheet two is June
Sheet three is July
Sheet 4 is August

I'm hoping she will be out before August.

If I had to insert a bill on any given page would that work?

I tried with commas said error in formula and had !NAME in the cell
Until I added the () it wouldn't work. I tried adding a Dollar to a Bill
and then saving change and yes it updated. I don't anticipating adding.
I might have to take away one, depending upon when I have to pay a
certain bill. (car insurance)

CyberTaz wrote:
Hi JL;

Valid considerations, but my interpretation of Phillip's description
was
that he had already established the number of rows being used in the
structure of his source sheets so that wasn't expected to change.
Even if he
were to Insert Rows/Cells on any of the source sheets, however, the
formula
on the Grand Total sheet would still continue to update properly
using any
of the methods involved as long as Absolute References weren't used.

Regards |:>)
Bob Jones [MVP] Office:Mac



On 6/7/09 10:20 AM, in article C65144FF.CE62%[email protected],

If I understand Phillip's question correctly, the number of items is
different on each sheet AND changes continuously. Hence the trouble in
getting a Grand total that includes all items on all sheets.

How about putting each sheet's total at the top, where it would be
in a
fixed location, rather than at the bottom? Bob's formula would then
work all
the time, but with this format:
=SUM(Sheet1:Sheet3!D1)

It means that, in this example, each sheet's D1 would have a
formula such
as:
=SUM(D3300)

Another alternative is to name the cells that include the total of
each
sheet. The Grand total would then be:
=sum1+sum2+sum3
Even though those cells would move Excel would always take care of
maintaining the proper reference for the Grand total.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.7



CyberTaz wrote on 2009-06-07 08:06:

.... etc.

=SUM(Sheet1:Sheet3!D25)

Phillip Jones, C.E.T. wrote:

how do I create a Grand total at the last page that covers all the
pages? *And how can I get then to dynamically change each time I
enter a
new item*?

 

[Phillip Jones, C.E.T.]

yes all your formulas worked if the colon was replace by the exclamation
point They wouldn't and until I run the error check which indicated it
should be ! instead of :. When I replaced them with the !'s then
everything worked. I did this on excel 2008. I don't know whether it
would be the same on 2004 or not (so far as using the !).

In any event thank you seems to be exactly what I wanted.

Yeah, that was boo-boo on my part 8>} I think I was trying to do too many
things at once... But at least I had it right the first time.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Missed a word:

I tried and would *not* work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

I tried and would work until the : was changed to!

=SUM(Sheet1!F19,Sheet2!F12,Sheet3!F11,Sheet4!F10)

See on a Mac : or :: is reserved for the system.

CyberTaz wrote:
Hi Phillip;

The first suggested method is a simple addition formula which does *not*
require the parens, although they certainly don't hurt. However, the
other 2
methods employ the SUM() function which requires the inclusion of
everything
beginning with = exactly as I wrote the examples in my reply � the only
difference being the actual sheet & cell references for the function
to use.

If you have time to play a bit this should provide the same results if
you
enter it (or copy/paste) exactly as shown here. It uses the same
sheet/cell
references as what you wrote below. Put this in place of your current
formula on your Grand Total sheet [substitute the Sheet1, etc. names with
the actual sheet names if necessary]:

=SUM(Sheet1:F18,Sheet2:F11,Sheet3:F10,Sheet4:F9)

Glad you got it working either way

Regards |:>)
Bob Jones [MVP] Office:Mac



On 6/7/09 2:44 PM, in article (e-mail address removed),

Thanks! This is formula I had to come up with.

=(Sheet1!F18+Sheet2!F11+Sheet3!F10+Sheet4!F9)

Sheet one was May
Sheet two is June
Sheet three is July
Sheet 4 is August

I'm hoping she will be out before August.

If I had to insert a bill on any given page would that work?

I tried with commas said error in formula and had !NAME in the cell
Until I added the () it wouldn't work. I tried adding a Dollar to a Bill
and then saving change and yes it updated. I don't anticipating adding.
I might have to take away one, depending upon when I have to pay a
certain bill. (car insurance)

CyberTaz wrote:
Hi JL;

Valid considerations, but my interpretation of Phillip's description
was
that he had already established the number of rows being used in the
structure of his source sheets so that wasn't expected to change.
Even if he
were to Insert Rows/Cells on any of the source sheets, however, the
formula
on the Grand Total sheet would still continue to update properly
using any
of the methods involved as long as Absolute References weren't used.

Regards |:>)
Bob Jones [MVP] Office:Mac



On 6/7/09 10:20 AM, in article C65144FF.CE62%[email protected],

If I understand Phillip's question correctly, the number of items is
different on each sheet AND changes continuously. Hence the trouble in
getting a Grand total that includes all items on all sheets.

How about putting each sheet's total at the top, where it would be
in a
fixed location, rather than at the bottom? Bob's formula would then
work all
the time, but with this format:
=SUM(Sheet1:Sheet3!D1)

It means that, in this example, each sheet's D1 would have a
formula such
as:
=SUM(D3300)

Another alternative is to name the cells that include the total of
each
sheet. The Grand total would then be:
=sum1+sum2+sum3
Even though those cells would move Excel would always take care of
maintaining the proper reference for the Grand total.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.7



CyberTaz wrote on 2009-06-07 08:06:

.... etc.

=SUM(Sheet1:Sheet3!D25)

Phillip Jones, C.E.T. wrote:

how do I create a Grand total at the last page that covers all the
pages? *And how can I get then to dynamically change each time I
enter a
new item*?