Summary of Multiple Worksheet Running Sum Totals

[karstens]

I have multilple worksheets that use the same format to keep track o
additions and retirements to plant records. However there is only a
entry for each year that has activity. The form has a running sum o
each sheet in colum I.
I need to have a summary sheet that keeps track of all running su
totals each year. So for instance, in 2005, I would need to know th
balance of each sheet with entries for 2005.

Example:
Sheet 1 2005 balance is in cell I30
Sheet 2 2005 balance is in cell I10
Sheet 3 2005 balance is in cell I1

Being the cell number can change from sheet to sheet, I don't know ho
to make a summary sheet giving me the totals I need since you neve
know which cell it is in. I have some workbooks that have 50
worksheets in them so I don't want to have to go to each page and fin
the sum.

Sorry if I didn't explain this well...Not a real whiz!
Thanks for any help or suggestions you can provide

 

[Otto Moehrbach]

What you want is fairly easy for Excel to do in VBA. But Excel will need to
know from which sheets to get the data. Using your example of wanting to
create a summary of all the 2005 sheets, how can Excel tell that some sheet
is or isn't a 2005 sheet? Does the sheet name contain "2005"? If so, give
us some examples of the sheet names.
Or maybe there is something in some cell of each sheet that indicates
what year that sheet applies to? Post back with more details of what you
have. HTH Otto

 

[karstens]

Thanks for your quick reply!

In one workbook, some of the sheet names are as follows:

General Office
Pole Yard
St George
Courtland
Klossner
Searles

Each sheet uses the same fomat...but here is the information from on
of the sheets (General Office):

_Year_ _Description_ _Additions_ __Retirements_
_Balance_

Row 7...Year:9/30/79 Decscription:Additions to Date Additions:
Retirements 0 Balance 20394.90


Row 8...Year:12/31/91 Decscription:Added Sewer Additions: 10587.9
Retirements 0 Balance 30982.87

This example does not have anything for 2005 but still gives you th
data I am working with.

My summary sheet should have the same columns, just should give a tota
of all the worksheets. So in this example, I should have a line tha
would result in:
1991......Additions are 10587.97 (plus any other worksheets wit
additions in 91)...Retirements 0 (plus any other worksheets wit
retirements in 91), balance of 30982.87 (Plus the balance of othe
worksheets with activity in 91).

I know I am not explaining this well and I apologize. If this is no
explanitory enough, please let me know. Thanks again....I appreciat
your help

 

[Otto Moehrbach]

Let me see if I understand what you have. In each sheet you have a date and
other information in each row. The years of the dates are NOT the same for
all rows. Do you want to sum the balances for Additions, Retirement as
well, not just Column I? But from what you say, the last row does not have
the balances for any particular year. Rather, it has the balance for all
the years that are on that sheet. Is that correct?
Is it that you want to sum up all the values in those rows that have a
date with a particular year?
This is more complicated than you can explain. If you wish, make up and
send me, via email, a small file with a handful of sheets and include a few
dozen rows of your data in each sheet in the same layout as your actual
file. Include the summary sheet that you want and include a couple of
examples in the summary sheet to show me what you want. Clearly show and/or
explain from where you got the data that you show in the summary sheet. My
email address is (e-mail address removed). Remove the "nop" from this address.
Otto

 

[karstens]

Thank you....I have sent the file via email. Let me know if it come
through ok. Thanks again