Sum the same cell across multiple sheets that are named in a certain pattern

riteshkp

Board Regular
Joined
Aug 22, 2008
Messages
51
I have a workbook that has 150 worksheets...75 of them are summary of statements and 75 of them are details of statements. The Summary and Detail sheets keep alternating. I have been tasked to prepare a combined Summary and Combined Detail sheet. All the 75 Summary Sheets and the 75 Detailed sheets have exactly the same structure. Using SUM(Sheet 1: Sheet 150) would not work., because the detailed sheets are there in between. Can anyone suggest how to go about this? (other than reorganizing all the worksheets).
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
This might help. It uses function INDIRECT to access the relevant cell in each sheet you care about. I have sheets called "detail101", "detail102", and "detail103". I want to add cell A1 in each of those sheets. That formula accesses the prefix and suffixes of each sheet; you must somehow create that list, which I assume is logically ordered as months or years or a series or some such thing, making it easy to type in one of them and copy down. Then do likewise for your summary sheets.
MrExcel posts18.xlsx
AB
1sheet namecell a1 value
2detail10150
3detail102500
4detail1035000
5
6
7
8sheet prefixesdetail
9
10sheet suffixes101
11102
12103
13
14cell to be summeda1
15
16
17sum of that cell in those sheets5550
detailT
Cell Formulas
RangeFormula
B17B17=SUM(INDIRECT(B8&B10:B12&"!"&B14))
 
Upvote 0
Some previous versions of Excel may require something like the following.

T202012b.xlsm
ABCD
1sheet namecell a1 value
2detail101505,550.00
3detail102500
4detail1035,000
1b
Cell Formulas
RangeFormula
D2D2=SUMPRODUCT(SUM(INDIRECT("'"&A2:A4&"'!"&"A1")))
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top