Forum Discussion
Sum across multiple sheets when adding sheets with dynamically changing sheet names?
Add the sum formula into the desired cell
- Left click on the FIRST sheet with the mouse.
- Hold Shift key and left click on the LAST sheet. Now select the cell D42 in the LAST sheet. Add a closing bracket to the formula and press Enter. Your sum formula should now look like this.
Considering the names you gave us, your formula will look like that
=SUM('test test:MJN Job 3'!D42)
In other words
=SUM('FirstSheet:LastSheet'!D42)Replace FirstSheet and LastSheet with the worksheet names you wish to sum between.
We can change this to be more dynamic, making it even easier to use. Instead of using the names of the first and last sheets, you can create two blank sheets to act as bookends for your calculation.
Add the sum formula into the desired cell
- Left click on the FIRST sheet with the mouse.
- Hold Shift key and left click on the LAST sheet. Now select the cell D42 in the LAST sheet. Add a closing bracket to the formula and press Enter. Your sum formula should now look like this.
Considering the names you gave us, your formula will look like that
=SUM('test test:MJN Job 3'!D42)
In other words
=SUM('FirstSheet:LastSheet'!D42)
Replace FirstSheet and LastSheet with the worksheet names you wish to sum between.
We can change this to be more dynamic, making it even easier to use. Instead of using the names of the first and last sheets, you can create two blank sheets to act as bookends for your calculation.
