2

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?

0
  • 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
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.