Saturday, August 13, 2011

yogi_Sum A Cell Across Multiple Sheets Within A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

scottborys said ...
sum across multiple spreadsheets using INDIRECT
I have a variable-sized list of sheet names in A9:A100 corresponding to certain sheets in the current spreadsheet. I'm trying to sum the same cell from each spreadsheet. For example, this would work if I knew that I would always have two sheets:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E17" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E17" ))
However, due to the fact that the number of sheets will be changing rather frequently, modifying the formula each time isn't an option. Any thoughts on how to achieve the sum across multiple sheets using INDIRECT?
After that, I'll need to figure out how to drag the formula down vertically, ie, the next formula would be the equivalent of:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E18" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E18" ))
But we can cross that bridge when we come to it! Anyone have any thoughts on how to tackle this beast?
------------------------------------------------------
here we go ...

I have housed the cell of interest in B2
I have listed all the sheets that might be of interest in range A9:A -- I have listed the sheets of interest in cell A9 and down and I can go to cell A99 or even beyond depending on how many sheets are involved in computing the SUM