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:
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:
This is great for one stat, however 10 numbers you are track for each player by month and there are 12 months. Then getting a sum across all players by month to show trends at the aggregate level by stat by month. Your method seems complicated to solve this problem. Since you know this so well, how would you approach this situation?
ReplyDeleteHi John Wienecke:
ReplyDeleteI can't say what approach I would use in your case -- it will depend on how your data is laid out and what you are trying to accomplish ... from what you have described, it will add to the complexity of the solution other than I can't say much without knowing more details.
Cheers!
Yogi
Hi Yogi, I came across your site while searching for a way to perform calculations on the same cell across multiple worksheets in Google Docs. Firstly, thank you for putting this resource together - although it's rather over my head I can see how helpful it is. Secondly, I have a quick question that I would love your insight on, which is somewhat related to this topic.
ReplyDeleteI'm working with a number of identical sheets that each contain 50 cells (each cell is a separate score ranging between 0 and 5). I don't know how many sheets will be added to the workbook (somewhere between 4 to 7, most likely), nor do I know what they will be called (all will be two-letter acronyms). I'm trying to create a summary sheet that looks at each of the 50 cells, and averages the score for that cell across the different sheets. The cell reference is easy enough but I don't know how to get the sheet references if I can't predict ahead of time what the sheet reference would be. Would you have any suggestions here?
Hi Hann Yew:
ReplyDeleteIn regard to the number of sheets and their respective names, you can use a script that can give you an array of all the sheet names in the workbook, and once you have that you can use a method like i have presented in this blog post.
I hope this helps.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com