Forum Discussion
Formula to add numbers from a specific cell in all my worksheets
- Feb 15, 2024
Insert a blank worksheet before the first one you want to want to subtract from D5, and name it First.
Insert a blank worksheet after the last one you want to want to subtract from D5, and name it Last.
Change the formula to
=D5-SUM(First:Last!B13)
When you want to add a new sheet, insert it between First and Last. It will automatically be included in the formula result.
You can enter 20 worksheet names Worksheet1, Worksheet2, Worksheet3 and so on in range I1:I20 with this formula.
=SUMPRODUCT(SUM(INDIRECT("'"&$I$1:$I$20&"'!B13")))
If you have for example 12 worksheets then enter their names in range I1:I12 and change the formula accordingly.
=SUMPRODUCT(SUM(INDIRECT("'"&$I$1:$I$12&"'!B13")))
If you work with a recent version of Excel it should work without SUMPRODUCT.
=SUM(INDIRECT("'"&$I$1:$I$20&"'!B13"))
OliverScheurich Thank you so much for your reply, I wasn't sure anyone would see my query.
So in my case each worksheet has been name after the work location which is different to every job. I haven't actually named them "worksheet 1, worksheet 2 etc". Does this change anything?
I tried the formula but it went #REF! and when looking at it, it seems to want to extract info from the I cell in my recap sheet (the I cell is empty).
I'm probably doing this wrong.
- OliverScheurichFeb 15, 2024Gold Contributor
Yes, the worksheet names in e.g. range I1:I12 must be actual worksheet names. An actual worksheet name must be entered in all referenced cells which means that no cell is blank.
If the names of the worksheets are named like months January to December (12 sheets) you can select e.g. January, April, October and December in range I1:I4 and calculate the result only for these 4 sheets. The range within the formula must be changed accordingly to I1:I4.