Tuesday, December 13, 2011

yogi_Summarize Stats From Data Across A Number Of Sheets In A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user JamieC said:
Create a summary worksheet from data across a dynamic number of worksheets
I'm tracking stats for my sports team. Using 1 sheet for each game, the players are listed in rows and game stat categories are the columns....along with some totaling being done in the columns.
I'd like to create a Season Summary worksheet. For instance, the summary may show that "Bob Smith" has scored 22 goals over 10 games.
The number of games for the season is NOT static...sometimes we pickup games. For this reason...it is a huge hassle to update formulas that SUM or Count based on the cell value in a'Sheet name' referenced across multiple worksheets.
So I wonder.... is there a way in the Summary Worksheet to pull data from all worksheets (say...those named Game1, Game2, Game3m etc) dynamically? Perhaps a formula on the Summary Sheet that performs a specific "search" across all those 'GAMEx' sheets and returns the SUMd value (where the data being SUMd is 1 cell to te right of the search criterial?
Any help would be much appreciated.
following is a solution to the problem: