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

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

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

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