Increment a number within a formula/sheet reference?

[dalmascus]

I am trying to make a summary table that can reference a bunch of different sheets that each contain the budgeting for a given month. When I enter the sheet references in my summary sheet, I've been using formulas such as =SUM('2020-06'!M$51:M$52) to specific which cells I'd like to add up from which monthly sheet. That example is from June, 2020. Is there a way to increment this automatically to make my summary table get the data from the same cells in each sheet? i.e by incrementing the sheet names to 2020-07, 2020-08, etc. (Or is there another way?)

 

[Joe4]

Welcome to the Board!

If the sheets are all in a row in your workbook, you can span it, like this:

=SUM('2020-06:2020-08'!M$51:M$52)

where the sheet name in red font is the first sheet name in the sequence, and the one in blue is the last one in the sequence.

 

[dalmascus]

Welcome to the Board!

If the sheets are all in a row in your workbook, you can span it, like this:

=SUM('2020-06:2020-08'!M$51:M$52)

where the sheet name in red font is the first sheet name in the sequence, and the one in blue is the last one in the sequence.

Thank you! This is quite helpful and I will keep it in mind. However, what J am looking to do is pull the SUM data from each Sheet into the Summary sheet, separately. I want to reference each one individually. I can type out each Sheet name manually, but I was curious to see if there was a more efficient way to do so. For Example, have cell H6 show the SUM of one sheet. Then H7 show the SUM of the next sheet. And so on.

 

[Joe4]

You can use the INDIRECT function to make your sheet reference a dynamic calculation.
Put this in cell H6 and copy down:

=SUM(INDIRECT("'2020-0" & ROW() & "'!M$51:M$52"))