Adding values for multiple worksheets

[ArenaNinja]

I work with some weekly worksheets and I figured my work was getting extremely repetitive. I've managed to get around most of the repetition for all except for one: my dreaded weekly summary. In this, I add values from all other 7 worksheets. I can do this perfectly well, but I can't use the same formulas in other files, even if the formulas would be perfectly identical, because the names of the worksheets are different.

Is there a way to add the values by only referring to the number of the sheet instead of referring to it by name? Note that I use these formulas in literally at least a thousand cells, so Macros might not be a good idea..

[Elkar]

I'm not sure if this will work for you or not, but here's one option.

Insert two new worksheets into your file. Call one of them "Begin" and the
other "End". Place "Begin" before all other sheets that you want to
summarize. Place "End" after all sheets that you want to summarize. Leave
these new sheets completely blank and then hide them.

Then, on your summary sheet, use the formula:

=SUM(Begin:End!A1)

This will add up all A1 cells in the sheets between Begin and End. This way
it never matters what names you give to those sheets in between.

HTH,
Elkar


"ArenaNinja" wrote:

>
> I work with some weekly worksheets and I figured my work was getting
> extremely repetitive. I've managed to get around most of the repetition
> for all except for one: my dreaded weekly summary. In this, I add values
> from all other 7 worksheets. I can do this perfectly well, but I can't
> use the same formulas in other files, even if the formulas would be
> perfectly identical, because the names of the worksheets are
> different.
>
> Is there a way to add the values by only referring to the number of the
> sheet instead of referring to it by name? Note that I use these formulas
> in literally at least a thousand cells, so Macros might not be a good
> idea..
>
>
> --
> ArenaNinja
> ------------------------------------------------------------------------
> ArenaNinja's Profile: http://www.excelforum.com/member.php...o&userid=33624
> View this thread: http://www.excelforum.com/showthread...hreadid=547675
>
>

[ArenaNinja]

Sorry.. but this wouldn't work out. To begin with, the formulas do not always add up values for ALL worksheets. Some worksheets are longer than others, but there's a consistency throughout a MONTH, not on all worksheets in the same workbook. Secondly, the worksheet's name needs to be self-explanatory (on terms of the information it contains in case one of them gets moved around). Here's an example of the first formulas:

Please Login or Register  to view this content.

That would be for the very first line and would work with your solution. However, there is no data for Sat and Sun for 9:00pm, so the formula is as follows:

Please Login or Register  to view this content.

Usually the patterns of data are consistent every week, but not for all cells. Any other bright ideas?

[Elkar]

Hmm... I'm not sure I understand why this wouldn't work. If there is no data
in D22 of sheets 'Feb 04:Feb 05' then you wouldn't need to exclude these from
the AVERAGE function. If these cells are blank, AVERAGE will ignore them.


"ArenaNinja" wrote:

>
> Sorry.. but this wouldn't work out. To begin with, the formulas do not
> always add up values for ALL worksheets. Some worksheets are longer
> than others, but there's a consistency throughout a MONTH, not on all
> worksheets in the same workbook. Secondly, the worksheet's name needs
> to be self-explanatory (on terms of the information it contains in case
> one of them gets moved around). Here's an example of the first
> formulas:
>
> Code:
> --------------------
> =AVERAGE('Feb 01:Feb 07'!D9)
> --------------------
>
> That would be for the very first line and would work with your
> solution. However, there is no data for Sat and Sun for 9:00pm, so the
> formula is as follows:
> Code:
> --------------------
> =AVERAGE('Feb 01:Feb 03'!D22,'Feb 06:Feb 07'!D22)
> --------------------
>
>
> Usually the patterns of data are consistent every week, but not for all
> cells. Any other bright ideas?
>
>
> --
> ArenaNinja
> ------------------------------------------------------------------------
> ArenaNinja's Profile: http://www.excelforum.com/member.php...o&userid=33624
> View this thread: http://www.excelforum.com/showthread...hreadid=547675
>
>