I am working on transitioning a spreadsheet from Excel to Google Sheets. Most of my formulas have translated well, but I am encountering an issue with the following formula:
=SUM('Sheet1:Sheet50'!D5)
This formula functions to aggregate a particular cell across various sheets, allowing for new sheets to be incorporated into the range with no modifications needed to the formula itself. I am struggling to find a method to achieve similar functionality in Google Sheets.
In Google Sheets, there’s no direct equivalent to summing a cell across multiple sheets like in Excel. One workaround is to use an Add-on that loops through each sheet and sums the specific cell. Alternatively, you can write an Apps Script to automate this. If scripting is challenging, you can manually list each sheet name in the formula, using something like =SUM(Sheet1!D5, Sheet2!D5, ..., Sheet50!D5) but this requires updating every time you add a new sheet.
You might conssider using INDIRECT and ARRAYFORMULA to help with this. It’s a bit complex tho, because you’ll need to list out all your sheet names inside the formula. Once set up, it updates if sheets are added or removed! Just needs initial setup.