Formula Request: Sum across different sheet tabs with dynamic range, without INDIRECT

willdow999

New Member
Joined
Sep 30, 2019
Messages
3
Hello,

I have a fund with 46 assets. All of the asset pages are built exactly the same.

To roll them up, i use a rollup page, with each line of the rollup exactly the same as each asset tab

To roll them up I sum Sum(BEG:END!E257) for December and Sum(BEG:END!F257) for January, and so on so I can drag the formula to the right and have it sum each month

However, sometimes, in the sheet tabs I want to add a row. And this messes up the row numbers of the master sheet.

How do I create a formula, that makes me BEG:END Sum dynamic WITHOUT using the indirect function? I do not want to use the indirect function because it is very volite and this sheet is already too big to function as is.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
What you can do is add the cell reference to the indirect.

e.g.
=SUM(INDIRECT("BEG:END!"&CELL("address",BEG!E257))

Then when you insert a row to the BEG sheet the formula would automatically change to
=SUM(INDIRECT("BEG:END!"&CELL("address",BEG!E258))
 

willdow999

New Member
Joined
Sep 30, 2019
Messages
3
Thank you very much for your kind response I really appreciate it.

However, how do I accomplish this task WITHOUT using the INDIRECT formula? The indirect formula takes up a lot of processing time and is very voltile
 

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
136
Use Data, Consolidate to create your summary sheet and set headings to top row and left column AND link to the source. Then if you add rows in the source sheets, the cell references on the consolidated sheet will update automatically. No formulae required!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,248
Messages
5,413,297
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top