summarise totals from many worksheets into one final worksheet

I need to add totals from many worksheets onto one final Summary worksheet.
the formula I create is too long for Excel. is there a way to shorten the
formula eg using the SUM function + Shift or Control keys? I believe Lotus
is able to do this.

If yo have many worksheets, the formula approach will be very long. Look at
this small piece of VBA:


Sub Macro1()
Dim w As Worksheet
t = 0
For Each w In Worksheets
w.Activate
t = t + Cells(1, 1)
Next
MsgBox (t)
End Sub


It adds up all the A1 cells in the workbook.
It doesn't care how many worksheets there are.
It doesn't need to know the names of the worksheets.

Perhaps you can adapt it for your needs.
--
Gary's Student


"NAAPS" wrote:

> I need to add totals from many worksheets onto one final Summary worksheet.
> the formula I create is too long for Excel. is there a way to shorten the
> formula eg using the SUM function + Shift or Control keys? I believe Lotus
> is able to do this.

Are your totals always in the same cells in each worksheet. I like to put my
totals in Row 1--so I know where they are.

And then...

I'd create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=Sum(start:end!A1)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheets so that people don't use it for real data.


NAAPS wrote:
>
> I need to add totals from many worksheets onto one final Summary worksheet.
> the formula I create is too long for Excel. is there a way to shorten the
> formula eg using the SUM function + Shift or Control keys? I believe Lotus
> is able to do this.

--

Dave Peterson

If the sums to be totalled are all in the same cell in each worksheet
=sum(firstsheet!:lastsheet!a1)

--
Don Guillett
SalesAid Software
[email protected]
"NAAPS" <[email protected]> wrote in message
news:[email protected]...
>I need to add totals from many worksheets onto one final Summary worksheet.
> the formula I create is too long for Excel. is there a way to shorten the
> formula eg using the SUM function + Shift or Control keys? I believe
> Lotus
> is able to do this.