# Fantasy League Spreadsheet Help

#### numberonegrandpa

##### New Member
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 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>
Table B is a function table that counts the occurrences of names in respective sheets. It is formatted as follows:
 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.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Replies
9
Views
193
Replies
1
Views
240
Replies
2
Views
562
Replies
1
Views
643
Replies
5
Views
407

1,207,255
Messages
6,077,313
Members
446,278
Latest member
hoangquan2310

### 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?

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