Adding values for multiple worksheets

A

ArenaNinja

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

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

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
 
A

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:

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:
 
E

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.
 
Top