Need 3D formula for a range of numbers across several worksheets

[Guest]

I constructed a set of XL2000 worksheets to record my household utility
bills. They're named Power, Gas, Water, Sewage, and Garbage Pickup (GP).

Recently, I added another worksheet named Summary, and inserted the
function=SUM(Power:GP!B4) into the corresponding cell beginning. It worked
beautifully.

However, when I tried to apply the same function to an older (but still
XL2000) set of worksheets, it returns NAME?

The 'older' set of worksheets contains monthly charges from 1992 to 2006.

The 'newer' set of sheets contains monthly charges for just one year, 2006.

I think I may see what the problem is--just don't know "how" to enter the
correct formula syntax. Here's what I think the problem is:
the new set of data is for only one year, whereas the old set covers from
1992 to 2006--a range of numbers. Perhaps a different formula must be
entered. (?)

For that set, it appears that I must enter a formula that does two
operations at once.
1.) Summarize the numbers in a range within a worksheet; 2.) Summarize the
worksheets.

I'm lost here ... would appreciate any constructive comments!

Thanks!

 

[Guest]

In the workbook that works, the formula
=SUM(Power:GP!B4)
is saying
Add the values in cell B4 on sheet named 'Power' and sheet named 'GP' and
all sheets in between them also!

If you've entered that same formula in the older workbook but it doesn't
have those sheet names, then you get the #Name error. You have 2 choices:
change the names of the sheets in the formula, or change the sheet names to
same as in the workbook that works (and put them in the same sequence).

If either your first or last sheet names have a space in them, that can be
causing a problem, if that's the case use ' to help Excel understand, like
this:
=SUM('My Power:GP'!B4)
note the single apostrophe in front of My Power and after GP. Doesn't hurt
anything if you put them in even if your sheet names don't have spaces in
them.

 

[Guest]

I'll also offer this - regarding the problem involving 1992-2006. Yes,
you're probably going to have to summarize them. But exactly how depends on
how things are set up. Do you have 12 months of entries on each of the
utility sheets, or do you have 12 sheets for Power, 12 for Gas, ... 12 for
GP??

Knowing how you have things set up in the workbook will really help give a
solution.