Indirect Sum across Sheet Range

[Scotster]

I'm using Start & End reference tabs to Sum values within identical layout sheets to gather combined values. Example:

Summary1_S
Sheet1
Sheet2
Sheet3
Summary1_E

Summary2_S
Sheet4
Sheet5
Summary2_E

Summary1
Summary2

Etc

The formula I have works great, simply =SUM(Summary1_S:Summary1_E!A$1)

However, I was wondering if I could use Indrect to do the same formula? That way I could use the Tab name reference to create the formula and would save me having to go into each of the summaries to change the formulae.


=SUM(Indirect("Summary1_S:Summary1_E!A$1") gives a REF error. I was wondering if there's a way around this?

 

[Fluff]

The Indirect function does support 3D referencing, so you cannot do that.

 

[Scotster]

Great, thanks for that. Saves me mucking around with it

 

[Fluff]

You're welcome & thanks for the feedback.