Totals Across Mutliple Sheets...

Using Excel 2000 with WinXP.
Must see attached for example.

GOAL:
To sum all "Total Area" values for each "Item #" from each sheet onto a separate Summary Sheet and being able to add more sheets on an ongoing basis.

You'll need to view summary sheet to get the gist of what I am trying to do.
The sheets do not necessarily need to be broken out into rows on the summary sheet, as long as the totals on the summary sheet are updated automatically whenever I change the "total area" value on any of the sheets.
I would like to add many more sheets but can't see how to do this without manually changing formula in each cell. I did it for the first one but kinda got stumped after that. I thought I could "sumif" across multiple sheets but from my trials and research, this does not appear to be an option.

Alternative:
On the summary sheet, I have also looked at the option of implementing "indirect" somehow, to drag ROW 2 down having the sheet number change only, but after many attempts, I am stumped on how to do this. This would be acceptable if the above is not an option. This way, I thought I could just drag down ROW 2 to increment the sheet numbers oNLY in the formula accordingly to as many rows as I needed.

Any help including alternative options would be greatly appreciated.

Thanks in advance,
Geo.

Bhuvi,

Thank you so much for your help. It appears to work fine, however I won't be able to put it to the test for a few days. I very much appreciate your time for helping me. It's nice to know there are kind people willing to help in this crazy world today.

Thanks,
Geo.

.

Originally Posted by geo.nova

Using Excel 2000 with WinXP.
Must see attached for example.

GOAL:
To sum all "Total Area" values for each "Item #" from each sheet onto a separate Summary Sheet and being able to add more sheets on an ongoing basis.

You'll need to view summary sheet to get the gist of what I am trying to do.
The sheets do not necessarily need to be broken out into rows on the summary sheet, as long as the totals on the summary sheet are updated automatically whenever I change the "total area" value on any of the sheets.
I would like to add many more sheets but can't see how to do this without manually changing formula in each cell. I did it for the first one but kinda got stumped after that. I thought I could "sumif" across multiple sheets but from my trials and research, this does not appear to be an option.

Alternative:
On the summary sheet, I have also looked at the option of implementing "indirect" somehow, to drag ROW 2 down having the sheet number change only, but after many attempts, I am stumped on how to do this. This would be acceptable if the above is not an option. This way, I thought I could just drag down ROW 2 to increment the sheet numbers oNLY in the formula accordingly to as many rows as I needed.

Any help including alternative options would be greatly appreciated.

Thanks in advance,
Geo.

Jaslake,

Wow. That is so cool! I've always wanted to work with excel macros as I know they can do amazing things but I've been stuck in the CAD world for so long. I really appreciate your taking the time to help out. It's wonderful to see that there are people willing to help out in this crazy world today. I'm anxious to try this once I get the rest of my data.

Thanks immensley,
Geo.

Originally Posted by jaslake

Hi Geo

If you're interested in a Macro solution try the Code in the attached. There's a Button on Sheet SUMMARY that does initial setup for all existing sheets including the appropriate Formulas. You need run this only once (unless you wish to start over)

After you've run Initial Setup, there's Change Event Code that adds new sheets to Sheet SUMMARY when you make a change to the new sheet.

The Code is as follows...

Please Login or Register  to view this content.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet, ws1 As Worksheet
Dim Rng As Range, sName As Range
Dim LR As Long

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Target.Column = 1 Then Exit Sub
Set ws = Sheets("SUMMARY")

With ws
LR = .Range("A" & .Rows.Count).End(xlUp).Row
Set Rng = .Range("A2:A" & LR)
Set sName = Rng.Find(ActiveSheet.Name, , xlValues, xlWhole, xlByRows, xlNext, False)
If sName Is Nothing Then
Application.EnableEvents = False
.Rows(LR + 1).EntireRow.Insert
.Range("A" & LR + 1).Value = ActiveSheet.Name
Set ws1 = ActiveSheet

For i = 1 To 9
.Cells(LR + 1, i + 1).Formula = "=SUMIF(" & ws1.Name & "!$A$6:$A$68," & i & "," & ws1.Name & "!$O$6:$O$68)"
Next i
Application.EnableEvents = True
End If
End With
Set sName = Nothing
End Sub
[/CODE]