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>
[TD="bgcolor: #d0e0e3"]Team 1[/TD]
[TD="bgcolor: #d0e0e3"]Team 2[/TD]
[TD="bgcolor: #d0e0e3"]Team 3[/TD]
[TD="bgcolor: #d0e0e3"]Team 4[/TD]
[TD="bgcolor: #d0e0e3"]Team 5[/TD]
[TD="bgcolor: #e6b8af"]Team 6[/TD]
[TD="bgcolor: #e6b8af"]Team 7[/TD]
[TD="bgcolor: #e6b8af"]Team 8[/TD]
[TD="bgcolor: #e6b8af"]Team 9[/TD]
[TD="bgcolor: #e6b8af"]Team 10[/TD]
</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>
[TD="align: center"]T5 Wins[/TD]
[TD="align: center"]B5 Losses[/TD]
[TD="align: center"]Team 1[/TD]
[TD="bgcolor: #c9daf8, align: center"] = COUNTIF ( A2:M6 , "Team 1" ) [/TD]
[TD="bgcolor: #f4cccc, align: center"] = COUNTIF ( A8:M12 , "Team 1" ) [/TD]
</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>
[TD="align: center"]Teams[/TD]
[TD="align: center"]H2H Wins[/TD]
[TD="align: center"]H2H Losses[/TD]
[TD="align: center"]H2H Ties[/TD]
[TD="align: center"]Top 5 Wins[/TD]
[TD="align: center"]Top 5 Losses[/TD]
[TD="align: center"]Top 5 Ties[/TD]
[TD="align: center"]Total Wins[/TD]
[TD="align: center"]Total Losses[/TD]
[TD="align: center"]Total Ties[/TD]
[TD="align: center"]Team 1[/TD]
[TD="align: center"]1 [I input this manually][/TD]
[TD="align: center"]0 [manual][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] = 'T5/B5'!B15 [/TD]
[TD="align: center"] = 'T5/B5'!C15 [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] = SUM ( B15 + E15 ) [/TD]
[TD="align: center"] = SUM ( C15 + F15 ) [/TD]
[TD="align: center"]0[/TD]
</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 |
B5 Week 1 |
<colgroup><col style="width: 100px"></colgroup><tbody>
[TD="bgcolor: #d0e0e3"]Team 1[/TD]
[TD="bgcolor: #d0e0e3"]Team 2[/TD]
[TD="bgcolor: #d0e0e3"]Team 3[/TD]
[TD="bgcolor: #d0e0e3"]Team 4[/TD]
[TD="bgcolor: #d0e0e3"]Team 5[/TD]
[TD="bgcolor: #e6b8af"]Team 6[/TD]
[TD="bgcolor: #e6b8af"]Team 7[/TD]
[TD="bgcolor: #e6b8af"]Team 8[/TD]
[TD="bgcolor: #e6b8af"]Team 9[/TD]
[TD="bgcolor: #e6b8af"]Team 10[/TD]
</tbody>
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
[TD="align: center"]T5 Wins[/TD]
[TD="align: center"]B5 Losses[/TD]
[TD="align: center"]Team 1[/TD]
[TD="bgcolor: #c9daf8, align: center"] = COUNTIF ( A2:M6 , "Team 1" ) [/TD]
[TD="bgcolor: #f4cccc, align: center"] = COUNTIF ( A8:M12 , "Team 1" ) [/TD]
</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>
[TD="align: center"]Teams[/TD]
[TD="align: center"]H2H Wins[/TD]
[TD="align: center"]H2H Losses[/TD]
[TD="align: center"]H2H Ties[/TD]
[TD="align: center"]Top 5 Wins[/TD]
[TD="align: center"]Top 5 Losses[/TD]
[TD="align: center"]Top 5 Ties[/TD]
[TD="align: center"]Total Wins[/TD]
[TD="align: center"]Total Losses[/TD]
[TD="align: center"]Total Ties[/TD]
[TD="align: center"]Team 1[/TD]
[TD="align: center"]1 [I input this manually][/TD]
[TD="align: center"]0 [manual][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] = 'T5/B5'!B15 [/TD]
[TD="align: center"] = 'T5/B5'!C15 [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] = SUM ( B15 + E15 ) [/TD]
[TD="align: center"] = SUM ( C15 + F15 ) [/TD]
[TD="align: center"]0[/TD]
</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.