Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Running Sum of cell in many spreadsheets

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).
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Running Sum of cell in many spreadsheets

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

On Mar 9, 2:13*am, AndreaV wrote:
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). *


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding entry in one cell to a running total in another cell TommyB Excel Worksheet Functions 1 March 12th 09 12:11 AM
Running Balance in One Cell MartyM Excel Discussion (Misc queries) 3 December 27th 07 10:02 PM
running the same record macro on different spreadsheets gwtechie72 New Users to Excel 2 November 18th 07 12:55 PM
Alert messages, running totals, connecting spreadsheets or cells cheryl Excel Discussion (Misc queries) 1 September 18th 07 12:20 PM
running VBA Functions in spreadsheets DesCF New Users to Excel 4 April 18th 07 12:47 PM


All times are GMT +1. The time now is 11:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"