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"))`

– KingOfAllTrades May 7 '15 at 20:55#REF!– Gary's Student May 7 '15 at 22:00