Adding same cell across dynamic sheet range

[Scotster]

Looking for some help if anyone can assist. I've searched around a bit but I can't find anything that quite does what I need. I don't know if what I'm after is impossible as it seems simple enough in my head.

I have a number of identical sheets. The number of sheets can vary, along with the values within them. I have a "Combined" sheet that sums the values for each of the cells across the sheets. Done manually this is very straight forward:

=SUM(Sht1:Sht3!J8)

I had hoped to use something like the following:

=SUM(INDIRECT("Sht1" & : & "Sht3!J8")), substituting the Sht1 and Sht3 values with cell values.

Ie
=SUM(INDIRECT(A1:B1 & "!J8")), where A1 = Sht1 and B1 = Sht3

I just get #REF! as the result no matter what sort of combination I'm going for.

Is there a way to do this? I need to make the cell address dynamic (which will be easy as it's going into the same cell) but making the quantity of sheets dynamic is proving challenging.

Thanks

 

[arthurbr]

A neat trick
Create two sheets, one called Start, one called End.( or whatever) and put all the other sheets between those two ( Start and End can be hidden)
In your "Combined" sheet enter =SUM(Start:End!J8)
You can now add sheets (between Start end End) or remove them , the formula will adapt.

 

[Scotster]

A neat trick
Create two sheets, one called Start, one called End.( or whatever) and put all the other sheets between those two ( Start and End can be hidden)
In your "Combined" sheet enter =SUM(Start:End!J8)
You can now add sheets (between Start end End) or remove them , the formula will adapt.

Funnily enough my colleague told me just that and it works a treat. Such a neat little fix. I've left them visible just so that it can be ensured they are added to the right area but working great.

Thanks for the help

 

[arthurbr]

You're welcome