Forum Discussion
Soso22190
Feb 15, 2024Copper Contributor
Formula to add numbers from a specific cell in all my worksheets
Good morning, I have an Excel spreadsheet with many worksheets. The last worksheet is a recap. In the recap worksheet I have a cell (for sake of argument E5) that needs to capture the overal...
- 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.
PeterBartholomew1
Feb 15, 2024Silver Contributor
The attached workbook follows HansVogelaar in that it uses a 3D range, but I have named it and then used TOCOL to return the values as an array and filter out the blank cells. Once that is done it is possible to treat the values as a normal array and display it as a list, sum it or use XLOOKUP to return the result from any specific sheet.