SUM across multiple sheets

[virtualoverride]

I have a complicated problem. I will try to explain it as simply as I can but bear with me as some parts are a bit difficult to describe...

I have an Excel workbook which will contain a number of sheets. The number of sheets may vary from time to time. One sheet, named "Total" will contain the totals. The other sheets could be named anything; we may assume they are named "Sheet1", "Sheet2"...

The "Total" sheet should be able to SUM the numbers from all the other sheets. For instance:
A1 on Sheet1 = 1
A1 on Sheet2 = 4
A1 on Sheet3 = 2
=================
A1 on Total = 7

I could do it thus:
SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1)
except that I do not know how many sheets there will be.

So there's the problem. Read on if you want to hear some of my ideas on ways to partially solve the problem.

One idea that I had was to list the sheet names in a row using a macro (I can handle the macro part). For instance:
<table border=1>
<tr align=center><td></td> <td>A</td> <td>B</td> <td>C</td></tr>
<tr align=center><td>15</td> <td>Sheet1</td> <td>Sheet2</td> <td>Sheet3</td></tr>
</table>

Then I tried stuff like this:
ADDRESS(ROW(), COLUMN(), 1, TRUE, A15)
which, if placed in cell A1 on "Total" would yield the value of 'Sheet1'!A1. However, this formula only yields the first value (the one in A15). I would still need some way to figure out how many sheets there are and SUM them up.


So, in summary:
- I need a function which, when placed in cell A1 on sheet "Total", will SUM the values of A1 on all other sheets. (When placed in A2, it will SUM the values of A2, etc.)
- The number of sheets is not known (though it can indirectly be figured out by using a macro; see next point)
- I can write macros, so if there is a way they must be integrated I have no problem with that. In fact, I know VB much better than I know workbook functions.

Thank you in advance for your help!

 

[just_jon]

See the 3-D reference in Excel. Basically, here it is:

=sum('Sheet2:Sheet3'!A1)

where A1 is what you wnat to add, sheet2 is the 1st sheet, sheet3 is the last sheet. will sum all sheets inbetween/inclusive.


_________________
This message was edited by just_jon on 2002-12-18 17:37

 

[IML]

Could you get away with?
=SUM('2nd Sheet:last sheet'!A1)

as long as you insert new sheet after "2nd Sheet" you should be okay.

 

[Mark W.]

See the Excel Help topic for "Refer to the same cell or range on multiple sheets by using a 3-D reference".

 

[kskinne]

see the following board message:

http://216.92.17.166/board/viewtopic.php?topic=29804&forum=2

this is a very similar situation to yours and with some modifications should work for you - post back if you need any help

hth
kevin

 

[Aladin Akyurek]

Insert 2 additional sheets and name them First and Last respectively. Put all other relevant sheets between First and Last.

Now you can use:

=SUM(First:Last!A1)

 

[virtualoverride]

Thank you all for your help!
I ended up using the blank "First" and "Last" pages on either end of my sheets.

This served a double purpose because I was able to use the "First" page as a sort of blank template--I have another macro that makes a copy of this page in order to insert a new sheet.

virtualoverride