Fantasy League Spreadsheet Help

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:
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>
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.
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top