Is there something similar to the dollar sign for reference dragging but between two or more sheets in the same spreadsheet?

Question

I have a Google Spreadsheet with 10 Sheets named from 1 up to 10. I also have a sheet called Result which has this formula in one of its cells on row 1: =SUM('1'!G2:G16). I would like to drag it downwards so that that row 2 in that same column uses data from sheet 2 instead; like this: =SUM('2'!G2:G16).

Row 3 should have something like =SUM('3'!G2:G16).

Row 3 should have something like =SUM('4'!G2:G16).

And so on, after dragging the first cell downward.

Is there any way to do this with a formula or special char in Google Sheets or do I have to manually edit each cell?

Answer 1

• create a new sheet and name it Index (or use some unneeded column from anywhere)
• populate it like this:

+----+---------+
|    |    A    |
+----+---------+
| 1  | Sheet1  |
+----+---------+
| 2  | Sheet2  |
+----+---------+
| 3  | Sheet3  |
+----+---------+
| 4  | Sheet4  |
+----+---------+
| 5  | Sheet5  |
+----+---------+
| 6  | Sheet6  |
+----+---------+
| 7  | Sheet7  |
+----+---------+
| 8  | Sheet8  |
+----+---------+
| 9  | Sheet9  |
+----+---------+
| 10 | Sheet10 |
+----+---------+

• then enter your Result sheet
• paste this in desired cell =SUM(INDIRECT('Index'!$A1;"!G2:G16")))
• and drag it down as usual

Answer 2

You can do this by just referencing the current row number e.g.

=SUM(INDIRECT(row()&"!G2:G16"))