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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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))
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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