numberonegrandpa
New Member
- Joined
- Sep 17, 2014
- Messages
- 1
I run a fantasy (American) football league and have been trying to create a spreadsheet that everyone can use to view the current standings, stats, etc.
The format of the league is as follows:
Players: 10-person league
Schedule: 13-week schedule
Standings: Head-to-head matches each week, no BYEs.
*additionally, each week the top 5 team scores get a win, and the bottom 5 get a loss (i.e., there are 10 wins and 10 losses per week)
I use ESPN for to compile the scores and head-to-head results, but need a separate spreadsheet for the alternate rules.
My current spreadsheet (which I completely made up from scratch so please excuse it) has the following sheets:
Sheet 1: Scores. This table lists all the teams in Column1 and their respective scores in Columns 2-14. I simply input the teams' scores on a weekly basis (from ESPN) and then sort the table scores on a weekly basis.
Sheet 2: Top 5/Bottom 5 (2 Tables: A & B). Using the data from Sheet 1, I copy/paste the top 5/bottom 5 names into Table A.
Table A is formatted as follows:
<colgroup><col style="width: 100px"></colgroup><tbody>
</tbody>Table B is a function table that counts the occurrences of names in respective sheets. It is formatted as follows:
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
Sheet 3. Overall standings (2 tables: C & D) this includes 2 tables. Table C is the "standings," the total wins and total losses (and column for total points scored to separate ties). The cells are linked to the total wins/total losses from Table D. Table D is a breakdown of those standings. The headers for Table D are:
<colgroup><col style="width: 100px"><col width="71"><col width="86"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
Here are my questions:
1) Ideally, I'd like everything in one spot, including schedule, scores, etc. And I would merely need to input the weekly scores into Sheet 1 and then everything else (wins/losses, top 5/bottom 5, standings, etc.) would automatically generate itself. How do I do this?
1A) Is there a way to link data across tables and sheets to a single cell. In other words, I want excel to recognize Team 1 across the entire document, "knowing" that it is all interconnected. My equations in tables B and C are merely simplistic workarounds, but the data isn't really connected.
2) As a dumb lawyer who never uses excel, am I doing anything wrong or not utilizing a feature in excel that would make my life much easier?
3) I share the spreadsheet via google docs, as it's the easiest way for people to view the tables, but Table C won't sort itself automatically. Even when I select the whole table and click "sort" it seems to only sort the team names, but doesn't changes the wins/losses and thus makes the data completely incorrect. I can great a table based on the data and sort it that way, but it seems very inefficient and unhelpful (especially because I can't copy/paste that table into an email).
I would appreciate any help on this as I'm pulling my hair out trying to figure it out. Happy to provide a link to the google doc if that helps.
Thanks in advance.
The format of the league is as follows:
Players: 10-person league
Schedule: 13-week schedule
Standings: Head-to-head matches each week, no BYEs.
*additionally, each week the top 5 team scores get a win, and the bottom 5 get a loss (i.e., there are 10 wins and 10 losses per week)
I use ESPN for to compile the scores and head-to-head results, but need a separate spreadsheet for the alternate rules.
My current spreadsheet (which I completely made up from scratch so please excuse it) has the following sheets:
Sheet 1: Scores. This table lists all the teams in Column1 and their respective scores in Columns 2-14. I simply input the teams' scores on a weekly basis (from ESPN) and then sort the table scores on a weekly basis.
Sheet 2: Top 5/Bottom 5 (2 Tables: A & B). Using the data from Sheet 1, I copy/paste the top 5/bottom 5 names into Table A.
Table A is formatted as follows:
T5 Week 1 |
Team 1 |
Team 2 |
Team 3 |
Team 4 |
Team 5 |
B5 Week 1 |
Team 6 |
Team 7 |
Team 8 |
Team 9 |
Team 10 |
<colgroup><col style="width: 100px"></colgroup><tbody>
</tbody>
T5 Wins | B5 Losses | |
Team 1 | =COUNTIF(A2:M6, "Team 1") | =COUNTIF(A8:M12, "Team 1") |
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
Sheet 3. Overall standings (2 tables: C & D) this includes 2 tables. Table C is the "standings," the total wins and total losses (and column for total points scored to separate ties). The cells are linked to the total wins/total losses from Table D. Table D is a breakdown of those standings. The headers for Table D are:
Teams | H2H Wins | H2H Losses | H2H Ties | Top 5 Wins | Top 5 Losses | Top 5 Ties | Total Wins | Total Losses | Total Ties |
Team 1 | 1 [I input this manually] | 0 [manual] | 0 | ='T5/B5'!B15 | ='T5/B5'!C15 | 0 | =SUM(B15+E15) | =SUM(C15+F15) | 0 |
<colgroup><col style="width: 100px"><col width="71"><col width="86"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
Here are my questions:
1) Ideally, I'd like everything in one spot, including schedule, scores, etc. And I would merely need to input the weekly scores into Sheet 1 and then everything else (wins/losses, top 5/bottom 5, standings, etc.) would automatically generate itself. How do I do this?
1A) Is there a way to link data across tables and sheets to a single cell. In other words, I want excel to recognize Team 1 across the entire document, "knowing" that it is all interconnected. My equations in tables B and C are merely simplistic workarounds, but the data isn't really connected.
2) As a dumb lawyer who never uses excel, am I doing anything wrong or not utilizing a feature in excel that would make my life much easier?
3) I share the spreadsheet via google docs, as it's the easiest way for people to view the tables, but Table C won't sort itself automatically. Even when I select the whole table and click "sort" it seems to only sort the team names, but doesn't changes the wins/losses and thus makes the data completely incorrect. I can great a table based on the data and sort it that way, but it seems very inefficient and unhelpful (especially because I can't copy/paste that table into an email).
I would appreciate any help on this as I'm pulling my hair out trying to figure it out. Happy to provide a link to the google doc if that helps.
Thanks in advance.