Consolidate (sum) specific range for all worksheets with common cell value

I have 100 worksheets with all rows and columns structured the same.  The first 20 sheets are designated as "Summary" sheets and the last 80 sheets contain the "Data" to be consolidated (sum) on the appropriate first 20 "Summary" sheets.  I need the values from each cell in the range "D11:O340" from all "Data" sheets to be sum totaled on the appropriate "Summary" sheet cell range "D11:O340" if the "Data" sheet cell "A1" equals the "Summary" sheet cell "A1".

For example, if "Data" sheets 21, 45, 48, 67 & 95 (could be any sheet) have the same cell "A1" value as "Summary" sheet 1 cell "A1", the values from each cell in the range "D11:O340" from each "Data" sheet are sum totaled on "Summary" sheet 1 cells "D11:O340"
rausch7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kerickCommented:
You could do this using hidden columns and these formulas
Example
on Sheet 1
A1 = 15
B1 = =SUM(Sheet2:Sheet4!B1)

on sheet 2
A1 = your value
B1 = IF(A1=Sheet1!A1,A1,0)

the same setup on each additional sheet.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You will need a VBA solution to achieve and it would be tough to suggest the exact solution unless you provide more details or better if you upload a sample workbook with few summary sheets and few data sheets. And in the sample workbook, don't forget to show the desired output manually on one of the summary sheet.

Also how can one distinguish between summary sheets and data sheets other than the index number of sheets i.e. first 20 would be summary and rest would be data sheets.

Do the summary sheets are named in a similar fashion and can be distinguished by their names also?
rausch7Author Commented:
Basically, the first 20 sheets are the sum total of the 80 data sheets

Let me see If I can explain it in a different manner.

"Summary" Sheet 1, cell A1 = 101

In cell D11 of "Summary" Sheet 1, I want to sum cell D11 from all "Data" sheets that have the value 101 in cell A1.

In cell D12 of "Summary" Sheet 1, I want to sum cell D12 from all "Data" sheets that have the value 101 in cell A1.

In cell E11 of "Summary" Sheet 1, I want to sum cell E11 from all "Data" sheets that have the value 101 in cell A1.

This would be repeated for all cells in the range D11:O340

Etc.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay try this code.
Place the following code on a Standard Module by following these steps..

1) Open your file.
2) Press Alt + F11 to open VBA Editor.
3) On VBA Editor, click on Insert and choose Module. This will open a code window on right side.
4) Paste the code into that code window.
5) Close the VBA Editor and Save your workbook As Macro-Enabled Workbook.

The code will take some time to process 80 data sheets and summarize their totals in 20 Summary Sheet so be patient during the code execution. You can monitor the progress of the code by looking at the Status Bar of the Excel Window. It will tell you that which Summary Sheet is being currently processed.

Code:

Sub GetSumInSummarySheets()
Dim i As Long, j As Long
Dim sCnt As Long, dCnt As Long

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

DoEvents

For sCnt = 1 To 20
    Sheets(sCnt).Range("D11:O340").ClearContents
Next sCnt

For sCnt = 1 To 20
    Application.StatusBar = "Working with Summary Sheet " & sCnt
    For dCnt = 21 To Worksheets.Count
        If Sheets(sCnt).Range("A1").Value = Sheets(dCnt).Range("A1").Value Then
            For j = 4 To 15
                For i = 11 To 340
                    Sheets(sCnt).Cells(i, j) = Sheets(sCnt).Cells(i, j) + Sheets(dCnt).Cells(i, j)
                Next i
            Next j
        End If
    Next dCnt
Next sCnt

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
MsgBox "Done!", vbInformation
End Sub

Open in new window

To run the code....

Press Alt + F8 to open Macro window, select the macro GetSumInSummarySheets and click on Run.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rausch7Author Commented:
Solution works great!!!!   Thank you.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad it worked for you. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.