Increment a number within a formula/sheet reference?

dalmascus

New Member
Joined
Feb 25, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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?)
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

If the sheets are all in a row in your workbook, you can span it, like this:
Rich (BB code):
=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

New Member
Joined
Feb 25, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Board!

If the sheets are all in a row in your workbook, you can span it, like this:
Rich (BB code):
=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
You can use the INDIRECT function to make your sheet reference a dynamic calculation.
Put this in cell H6 and copy down:
Excel Formula:
=SUM(INDIRECT("'2020-0" & ROW() & "'!M$51:M$52"))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top