Can I reference sheets and/or row/column IDs in calculations from other cells?

[marvel]

I have a large spreadsheet (“totals”) that references many other sheets in calculations. Example cell:
=SUM($sheet1.M77,$sheet2.M77,$sheet3.M77,$sheet4.M77)

Maintaining this is a pain; every time I add a new sheet, I have to update every cell in “totals” to reference it.

Is it possible to use the value of other cells as reference IDs in a calculation? If I could put all of the sheet names in a cell or group of cells and reference them in my SUM, that would be ideal. Even better if I could also reference the row/column ID from another cell. Such as:

=SUM($A1.B1) where A1=“sheet1,sheet2,sheet3, …” and B1=“M77”.

or =SUM($A1:$A5.M77) where A1-A5 are “sheet1”, “sheet2”, etc.

Any advice would be most appreciated.

[anon73440385]

Hello,

in your special case you can do the following:

• put string Sheet1.M77:Sheet4.M77 into let’s say cell A1 and use
• formula =SUM(INDIRECT(A1) to calculate the total of all sheets

If you now add a new Sheet5 change the text in A1 to Sheet1.M77:Sheet5.M77.

Attention

• This only works in your special case where all cells to be added are in the same cell on all sheets (M77). It will produce unexpected results, if this is not the case. So don’t try, if this condition isn’t met.
• There is no automatic update on references if you add/delete columns
• This does not work, if you add a sheet between first and last sheet, having a value in cell M77, which should not be included in total sum.

[marvel]

That helped, thank you!