Avatar of Steven S
Steven S
Flag for United States of America asked on

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"
Microsoft ExcelVisual Basic ClassicVBA

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
kerick

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)

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?
Steven S

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Steven S

ASKER
Solution works great!!!!   Thank you.
Subodh Tiwari (Neeraj)

You're welcome. Glad it worked for you. :)