Copying a total from another Sheet

[bcemail]

I have a workbook setup with about 15 worksheets. Each sheet (except the 1st) represents an office budget, and has a total at the bottom of the cost column (basic "sum" total). The first sheet has the totals of all the offices, which are then totaled. I'm trying to determine if it is possible to have the 1st sheet update an office's total, when a value in that particular office's sheet is changed. I've tried referencing each sheet's total on the 1st sheet, but it just copies the exact formula, so it totals the fourth column on the 1st sheet (which is blank).
I'm not sure if it's possible to copy a total formula, and then to have those copied totals totaled, for a grand total figure.
Hope this makes sense. If not, just let me know and I'll try and explain it better.
Thanks!

[Don Guillett]

try putting the total in the same place on each sheet
=sum(firstsheet:lastsheet!a1)

--
Don Guillett
SalesAid Software
[email protected]
"bcemail" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook setup with about 15 worksheets. Each sheet (except
> the 1st) represents an office budget, and has a total at the bottom of
> the cost column (basic "sum" total). The first sheet has the totals of
> all the offices, which are then totaled. I'm trying to determine if it
> is possible to have the 1st sheet update an office's total, when a
> value in that particular office's sheet is changed. I've tried
> referencing each sheet's total on the 1st sheet, but it just copies the
> exact formula, so it totals the fourth column on the 1st sheet (which is
> blank).
> I'm not sure if it's possible to copy a total formula, and then to have
> those copied totals totaled, for a grand total figure.
> Hope this makes sense. If not, just let me know and I'll try and
> explain it better.
> Thanks!
>
>
> --
> bcemail
> ------------------------------------------------------------------------
> bcemail's Profile:
> http://www.excelforum.com/member.php...o&userid=28992
> View this thread: http://www.excelforum.com/showthread...hreadid=487212
>

[Sloth]

You should be able to reference the cells with the total in them. For
instance a cell in Sheet1 can reference A4 in Sheet2 with
=Sheet2!A4

"bcemail" wrote:

>
> I have a workbook setup with about 15 worksheets. Each sheet (except
> the 1st) represents an office budget, and has a total at the bottom of
> the cost column (basic "sum" total). The first sheet has the totals of
> all the offices, which are then totaled. I'm trying to determine if it
> is possible to have the 1st sheet update an office's total, when a
> value in that particular office's sheet is changed. I've tried
> referencing each sheet's total on the 1st sheet, but it just copies the
> exact formula, so it totals the fourth column on the 1st sheet (which is
> blank).
> I'm not sure if it's possible to copy a total formula, and then to have
> those copied totals totaled, for a grand total figure.
> Hope this makes sense. If not, just let me know and I'll try and
> explain it better.
> Thanks!
>
>
> --
> bcemail
> ------------------------------------------------------------------------
> bcemail's Profile: http://www.excelforum.com/member.php...o&userid=28992
> View this thread: http://www.excelforum.com/showthread...hreadid=487212
>
>

[William Horton]

It sounds like you are trying to copy the formula / cell from the other
sheets to sheet 1. Instead try typing in a cell in sheet 1 "=Sheet2A1" where
A1 = the cell reference of the value that you want to return. Then whenever
the value in Sheet2A1 changes, it should also change on sheet 1 where you
entered the formula.

You could also put on sheet 1 if you wish "=Sheet2A1 + Sheet3A1 + etc".

Hope this helps.

Bill Horton

"bcemail" wrote:

>
> I have a workbook setup with about 15 worksheets. Each sheet (except
> the 1st) represents an office budget, and has a total at the bottom of
> the cost column (basic "sum" total). The first sheet has the totals of
> all the offices, which are then totaled. I'm trying to determine if it
> is possible to have the 1st sheet update an office's total, when a
> value in that particular office's sheet is changed. I've tried
> referencing each sheet's total on the 1st sheet, but it just copies the
> exact formula, so it totals the fourth column on the 1st sheet (which is
> blank).
> I'm not sure if it's possible to copy a total formula, and then to have
> those copied totals totaled, for a grand total figure.
> Hope this makes sense. If not, just let me know and I'll try and
> explain it better.
> Thanks!
>
>
> --
> bcemail
> ------------------------------------------------------------------------
> bcemail's Profile: http://www.excelforum.com/member.php...o&userid=28992
> View this thread: http://www.excelforum.com/showthread...hreadid=487212
>
>

[Gord Dibben]

Bill

An easier method for your formula if A1 of each sheet is to be summed.

=SUM(Sheet2:Sheet15!A1)


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 07:01:09 -0800, "William Horton"
<[email protected]> wrote:

>You could also put on sheet 1 if you wish "=Sheet2A1 + Sheet3A1 + etc".