Using a common worksheet name in a SUM formula

[Guest]

I would like to be able to use specific names for worksheet tabs (i.e. Monday 05-31-04) and yet, use a basic name (i.e. Sheet1, or Tab1) in a formula that SUMS the totals of a range of cells across numerous worksheets. I am open to almost any type of solution.
What I have is a workbook that tracks some call center statistics. There is a worksheet for each day of the month, and I am trying to create a worksheet that will allow me to tabulate data based on weekly numbers
Each day, Monday through Saturday occupy their own worksheet. They are named Monday 05-31-04, Tuesday 06-01-04, Wednesday 06-02-04, etc.
I would like to do a SUM statement that refers to each worksheet in a common name that would not need to be updated every month so that I can create a base "template" workbook and populate it as needed.
My SUM statement at present appears like this
=SUM('Monday 05-31-04'!B20,'Tuesday 06-01-04'!B20,'Wednesday 06-02-04'!B20,'Thursday 06-03-04'!B20,'Friday 06-04-04'!B20,'Saturday 06-05-04'!B20
Is there a "common" name I can use in the SUM statement to avoid having to modify each workbook manually updating the dates? Something like this
=SUM('Sheet1'!B20,'Sheet2'!B20,'Sheet3'!B20,'Sheet4'!B20,'Sheet5'!B20,'Sheet6'!B20

Thanks very much for taking a look at this.

 

[Peo Sjoblom]

Insert 2 dummy sheets that are empty, one before the first data sheet and
one after, then use

=SUM(First:Last!B2)

that formula will sum all B20

also your formula can be shortened to

=SUM('Monday 05-31-04:Saturday 06-05-04'!B20)

I use the dummy variety at times, it is handy. That way you can call
the sheets in-between whatever you want.





--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

I would like to be able to use specific names for worksheet tabs (i.e.

Monday 05-31-04) and yet, use a basic name (i.e. Sheet1, or Tab1) in a
formula that SUMS the totals of a range of cells across numerous worksheets.
I am open to almost any type of solution.

What I have is a workbook that tracks some call center statistics. There

is a worksheet for each day of the month, and I am trying to create a
worksheet that will allow me to tabulate data based on weekly numbers.

Each day, Monday through Saturday occupy their own worksheet. They are

named Monday 05-31-04, Tuesday 06-01-04, Wednesday 06-02-04, etc..

I would like to do a SUM statement that refers to each worksheet in a

common name that would not need to be updated every month so that I can
create a base "template" workbook and populate it as needed.

My SUM statement at present appears like this:
=SUM('Monday 05-31-04'!B20,'Tuesday 06-01-04'!B20,'Wednesday

06-02-04'!B20,'Thursday 06-03-04'!B20,'Friday 06-04-04'!B20,'Saturday
06-05-04'!B20)

Is there a "common" name I can use in the SUM statement to avoid having to

modify each workbook manually updating the dates? Something like this?

 

[Guest]

Nice, A very simple, yet useable solution. I will try both methods although I am thinking dummy sheet might be the easiest of the two.
As always,
Thanks so much.