I’m in the process of transitioning a spreadsheet from Excel to Google Sheets, and while the majority of my formulas transitioned without issue, one particular summing technique remains problematic. In Excel, I used a formula that added the value of a specific cell across a set of sheets, automatically adapting as I added more sheets. For instance, I previously used a formula like the one shown below:
=SUM('Alpha:Omega'!F8)
I need to achieve a similar functionality in Google Sheets but have not been able to replicate this dynamic behavior. Any insights would be greatly appreciated.
After years of using Google Sheets for various projects, I can say that dynamic sheet range references like Excel’s ‘Alpha:Omega’ syntax are not natively supported. In my situation, dealing with multiple sheets meant device alternative workarounds. Initially, I attempted to use INDIRECT with a manually maintained list of sheet names, but this approach quickly became limiting. Ultimately, I wrote a small Apps Script that loops through the sheets to sum the desired cell. This method proves to be scalable and much easier to maintain whenever new sheets are added to the workbook.
hey, try sumproduct with indirect referencing a range of manually listed sheet names. its a bit clunky but works until google adds dynamic range sheets. might not be perfect but saves u from writing a complex script
I encountered a similar challenge when transitioning from Excel to Google Sheets, and one method I found useful was setting up a central location with all the sheet names and using INDIRECT in combination with an array formula. This requires a bit of manual setup, but it allows for more flexibility as you add sheets. Although it isn’t entirely dynamic, it has worked reliably for me when I needed to update the list. Over time, refining the process reduced the maintenance involved and integrated well with other formulas in my workbook.
In my experience with Google Sheets, one solution that worked well was to set up a hidden helper sheet to automatically capture all sheet names. I created a custom function using the Sheets API to list all worksheets, applying a filter to exclude non-relevant tabs. Then I combined that list with INDIRECT inside an ARRAYFORMULA to pull in the cell references and sum them. Although not as straightforward as Excel’s native syntax, this method minimizes manual updates and adapts to new sheets once they adhere to the naming convention.
hey, i tried a method using importrange to pull data into a master sheet. once u got them in one place, summing them is easy. its not completely auto, but cuts down on manual updates when adding new sheets