I can easily sum a given cell, say B9, across multiple worksheets with something like:
=SUM(Sheet7:Sheet11!B9)
This is the equivalent to :
=Sheet7!B9+Sheet8!B9+Sheet9!B9+Sheet10!B9+Sheet11!B9
Which is the sum of cell B9 in all the worksheets from Sheet7 through Sheet11.
I need to create something like the first formula, but with the sheet names in cells. So if A1 contains:
Sheet7
and cell A2 contains:
Sheet11
Then the formula in A3 would use these cells. What I have tried so far is:
=SUM(A1 & ":" & A2 & "!" & B9)
and
=SUM(INDIRECT(A1 & ":" & A2 & "!" & B9))
Neither of these work.
I can easily create a VBA function to do this, but the workbook must be able to operate in a macro-free environment.
=SUM(INDIRECT(A1 & ":" & A2 & "!" & "B9"))