Running Sum of cell in many spreadsheets

[AndreaV]

I have a workbook that is comprised of daily bank deposits. Each worksheet
has a cell H3 that has the total money collected. I would like to create a
worksheet that will add the value in every H3 cell in the workbook. The
formula must capture all worksheets in the workbook as they are added. In
other words, I'd like a total of deposits for the year on an ongoing basis.

Can you also create a graph of the totals by month? The tab on each
worksheet reflects the date of the deposit (Mar 8 2010) or (Mar 5-6 2010).

 

[Pete_UK]

Insert two new sheets, and name one of them "start" and the other one
"end". Position these sheets so that they form a "sandwich" around the
sheets that you want to add from, with your Summary sheet outside the
sandwich. Then in your Summary sheet you can use this formula:

=SUM('start:end'!H3)

If you add new sheets then make sure that they are positioned inside
the sandwich.

For your second query, you will need to produce a contiguous range of
those data values in your Summary sheet, and then use this as the
source data for your graph. To do this you will need to list the names
of each sheet. Suppose this is in column A, then if your sheet names
are purely dates you can put the earliest date in A1, and in A2 you
can just have a formula like:

=A1+1

and copy this down. However, if you have names like "Mar 5-6 2010"
this will not work.

Then in B1 you can have this formula:

=INDIRECT("'"&TEXT(A1,"mmm d yyyy")&"'!H3")

and copy this down.

Then starting in D1, for example, you can list the months that you
want to graph in column D, like "Jan", "Feb", "Mar" etc, and put this
formula in E1:

=SUMPRODUCT(--(TEXT(A$1:A$50,"mmm")=D1),B$1:B$50)

and copy this down. Then use D1:E12 as the source data for your graph.

Hope this helps.

Pete