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.
Juliano-Petrukio
Hi Juliano,
Imagine that you have 2 cells, where the first contain the name of the first tab and the second the name of the second one. It can be use full if the tab name change and you have a lot of cells where you use the formula, as if you create a template.
Is it possible to write a formula which use these two cells? It seems that it does not work.
I tried the below formulas:
=SUM(INDIRECT("'"&$A$1 & ":" & $B$2 & "'!D42"))=SUM(CONCATENATE("'",$A$1,":",$A$2,"'!D42"))
SUM(CONCATENATE("'",$A$1,":",$B$2)&INDIRECT("'!D42"))
No of these formulas works.
Do you have an idea about how to do?
Thank you so much
INDIRECT() doesn't work with 3D references, i.e. with ones like Shee2:Sheet6. One of workarounds if you have sequentially numbered sheets. When you may generate array of references on such sheets and sum result
=SUM(INDIRECT("Sheet" & SEQUENCE(5,,2) & "!D42" ))
However, that's not practical, especially for the templates.
Another workaround is to create two empty sheets, start and end, we may hide them. Actual sheets will be in between. With that formula could be
=SUM(start:end!D42)
Also not perfect, but at least something.
Another way is to generate list of all sheets with VBA or Automate and use them as in first case.
Thank you Sergei,
About the first formula, unfortunately, I do not have any sequential sheet names, but it is a solution which could be necessary to keep in mind for any future spreadsheet.
The second solution is good, if we hid the sheets, it will as if there was nothing, then it will be complitely transparent for the users.
Thank you for your answer, whichh was very useful.
Boulawan , you are welcome.
Problem with second solution is if user would like to add another tab, most probably it'll be after end. When formula result is incorrect.
