totals

[Moh]

i have 1 main worksheet and 78 secondary worksheets labelled as week1 to
week78.

how do i add totals from cell L5 from all sheets to my Main sheet

The total should be shown in the main sheet

 

[Dave Peterson]

I'd do this:

move Main so that it's the leftmost sheet in the workbook
Insert a new worksheet to the right of Main. Name it Start.
Insert a new worksheet to the right of every worksheet (it'll be the last
worksheet in the workbook). Name it End

Then use this formula on Main:
=sum(start:end!L5)

 

[RagDyer]

OR, just enter this on the "Main" sheet:

=Sum('*'!L5)

 

[Dave Peterson]

This was posted by Lori in the original (well, original for me) thread:


where the '*' automatically converts to all sheets other than the
activate one. Excel 2002 seems to have introduced a bug where this can
crash the application. Does anyone else have the same problem?

OR, just enter this on the "Main" sheet:

=Sum('*'!L5)

 

[RagDyeR]

Sent this yesterday from an XL2K machine, where everything worked fine, and
I did forget about the caveat!
When I first saw Lori's post, I was on an XL97 machine where everything
worked as advertised.

I just ran this on an XL02 /SP3 machine and didn't experience anything fatal
(crash).
Don't know what would happen in other builds of XL02?

Worked OK if the focus sheet was on the border (beginning or end) of the
tabs.
However, there *is* a bug in XL02 *if* the sheet in focus is within the
sandwich.
The range (sheets) to the left of the focus sheet are missing the row number
of the cell to sum, while the range to the right of the focus sheet is OK
.... but this does produce a #Name? error.

SO, I would venture to say that this type of formula input should perhaps be
relegated to an item to "wonder at", but not use!

Thanks for bringing up that caveat Dave, and to all ... sorry I forgot to
mention it.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


This was posted by Lori in the original (well, original for me) thread:


where the '*' automatically converts to all sheets other than the
activate one. Excel 2002 seems to have introduced a bug where this can
crash the application. Does anyone else have the same problem?

OR, just enter this on the "Main" sheet:

=Sum('*'!L5)