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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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,217
Messages
5,413,134
Members
403,464
Latest member
TheRepairGuy

This Week's Hot Topics

Top