Dynasty Tracking Sheet

[doomguard]

Re: Dynasty Tracking Sheet

Post it. I wouldn't mind looking at it

For some reason I can't post attachments.....? Perhaps as I am a new member of the forums?

[BallinVol]

Re: Dynasty Tracking Sheet

For some reason I can't post attachments.....? Perhaps as I am a new member of the forums?

Not enough posts ... you can always host the file on another site and provide the link here. You probably won't be able to directly link it, but you can provide the URL to the file.

[xfhdxzh]

Re: Dynasty Tracking Sheet

What are some things people would want on it? I'm on call this weekend and don't mind working on it.

[jasonseidman]

Re: Dynasty Tracking Sheet

My spreadsheet has evolved for NCAA '14 and I've attached a copy of it here.

It would've been way too much work to whittle it down to a template and I would have to take out some stuff that I added later so I'm just attaching the entire thing just in case anyone wants to whittle it down themselves. Examples of things I added as my dynasty progressed are:
I went from OC to HC. I don't track win/loss records as an OC.
I added a 4 team and then later an 8 team playoff.

The instructions I listed here:
http://www.operationsports.com/forum...6&postcount=24

still apply. Below are new ones that only apply to this new version of the spreadsheet.

In the schedule tab, under "POS" I put "HC" for Head Coaching jobs and OC or DC for coordinators. If that slot has "HC" then all records will be recorded (overall, home/away, against various opponents, etc). If I'm a coordinator (technically if I'm anything other than "HC") then records won't be recorded. If you want to track coordinator records, then edit the hidden "HC" column (column S) to be all '1's.

Also, I tracked my record at different schools that I coached. I didn't do any pattern matching for this. I simply used the correct rows of columns W,X for that school:


IE. for Michigan, I've been there since row 245 so I sum the win/loss from rows 245+.

Since I've added a playoff, I've added new categories under "Week" in the schedule tab.

If a game is listed as "CCG", "Quarters", "Semis", "Finals", or "Bowl" then the home/away records won't be updated.
Also, under the H/A column, if you play a neutral site game, put "N" for that game and it won't count towards home/away.

For opponent team win/loss records, there are 2 columns for each team. IE. Illinois is in AN,AO. To change this to a different team (IE. Air Force), highlight columns AN and AO and then just do a simple search/replace to change all "Illinois" to "Air Force". Then change "Illinois" name under the standings (IE. box L10) to Air Force.
To add new teams, there are a bunch of columns with the same formulas that have "TEMP" in lieu of a team name. Just search/replace all "temp" with your new name, then add this team in the standings section and sum the correct columns for wins and losses.
Then, whenever you add teams to your yearly schedule, just make sure the name matches and it'll track. IE. If I enter "Air Force" as my "opponent" it'll keep track, but if I entered "AF" or "AirForce" or some other type-o, it won't recognize it.

I think that's it for the Schedule tab.

I think everything else should be the same as my '13 spreadsheet (SMU). The only new thing is there's a "My Team" column in all of the stat tabs. For the source tabs, I just list the name. For the sortable "non-source" tabs I've color coded the team name just to make it easier to identify. This is simply a text recognition conditional format for all of column B that you can change for your own teams.


You can't read the entire rules but it bascially, if cell value contains "Michigan" it'll be maize and blue, contains "UConn" it'll be blue and white, etc.

This spreadsheet is so cool! Love it! Thanks for sharing

[doomguard]

Re: Dynasty Tracking Sheet

So annoying though it is that I can't share my sheet, here's what I have so far:

- team index pages for all 12 teams, each of which links to :
- a recruitment page
- a record book
- a schedule
- a season review/notes page
- a bowl record page

- a league record book
- a career stats tracker
- a head to head stats record
- a strategy/formation guide
- a contracts page
- a recruitment summary
- a pipelines listing

I'm still working on it, so if there's anything I've missed let me know. One day I may even be allowed to share it!

[Son of Snow]

Re: Dynasty Tracking Sheet

I don't think I've ever been more impressed by a spreadsheet before..

Jello, master of Excel!

[itsbigmike]

Re: Dynasty Tracking Sheet

That spreadsheet from Jello is fantastic. I can't wait to dig into that more.

[jello1717]

Re: Dynasty Tracking Sheet

Some have asked for my updated spreadsheet, so here it is. This spreadsheet uses a LOT of formulas to keep track of stuff automatically for you (wins/losses, stats, records, etc).

Dynasty Template is the template and it's a pretty bared bones spreadsheet, but it has some stuff in there applicable to my dynasty (user team names, etc) to show how it should be setup.

Coach Mode Career '14 is the file for one of my old dynasties. This is a couple decades into the dynasty and shows how it should look when everything's filled in.

A lot of these instructions are already in this thread, but I'm gonna basically redo them.

I made this for myself so I know all the ins and outs of it. I'll try to explain stuff that's not obvious, but I'm sure I'll miss some stuff. The examples I give will be from the completed "Coach Mode Career '14" file, so use that as a reference. I'll just go through the tabs 1 by 1 and explain stuff.

If you're familiar with excel and its formulas (the more familiar the better) it really helps. If you get lost in spreadsheets, you probably shouldn't even bother with this. As long as you can search/replace, you should be fine.

Schedule
This has every season's schedule, as well as your various career records (vs. top 25, @ home, @ each of your schools, against other teams, etc).



Upper left: "Current Season" is just set to be equal to the current season record for your current year (this isn't shown on the screen because my monitor is too small which is why I put it in the upper left). Every year set those squares ("7-1" and "4-1" in this pic) to be equal to those of your current season (IE Current wins = square D620) and it'll be updated after each game.

Every season fill out your current schedule. This is the "Conf, Rival, H/A, and Opponent columns.
Use "X" to denote Conference foes and Rivals in those columns.
For H/A, "N" is for neutral site regular season games. "@" is for road games. Blank is for home.
Weekly, as you play the games, include both teams' rankings and your POTG (if you want).
GGS is where I put notes about the game (My Greatest Game Score if applicable, # of OTs).
Add your score and your opponents scores in their columns the spreadsheet will automatically figure out if you won ("W") or lost ("L).

Note the encircled note in the top center. There are hidden columns that handle most of the calculations (note how it skips from column "P" to column "HN". You can highlight columns P and HN -> right click -> "unhide" to see all of the formulas. Most of the column names are hopefully self explanatory (IE. Win, Conf?, Conf L, Rivalry W, etc). Here are some notes about them:
I only tracked wins/losses if I was the HC. So the"HC?" column looks @ column C to see if it says "HC" or "OC" or "DC". If you want to track wins/losses, makes sure column C has all "HC"s.
To see what each column is looking @ you can check the formulas in each square, but there are a LOT of nested "if" statements in some of them.

I track my record against every team that I play. These are displayed at the top, and are calculated in the hidden columns. I don't have every team listed so you'll have to edit these if you play teams not in my list.

• The rightmost hidden columns are placeholders. To add a new team, highlight both columns ("W" and "L") and do a search/replace, replacing all "temp" with your new team (IE. "New Mexico").
• If you run out of "TEMP" placeholders, or simply know you'll never play some of the teams I've included, you can replace them by doing a similar search/replace, just replacing the old team (instead of "temp") with your new team.
• To display these, you'll need to change/add the new team (IE. New Mexico) at the top and change the "W" and "L" column sums to the new ones. IE. I could change Minnesota (L8) to "New Mexico" and then change their win total (M8) from "=SUM(BF6:BF1454)" to the new columns I search/replaced to be New Mexico (IE. maybe it's now =SUM(HH6:HH1454))
• The various team records are just dumb pattern recognition which presents a couple of problems. 1. if you mispell the team name in your schedule (IE. "Naw Mexico") it won't track your win/loss. 2. When doing a search/replace to remove my teams for yours, be careful you don't replace too many. IE. replacing "Michigan State" with "New Mexico" is fine. But, replacing "Michigan" with "New Mexico" will work for Michigan, but will also turn "Michigan State" into "New Mexico State" and "Western Michigan" into "Western New Mexico", etc.
• To make sure every opponent registers, every year after I enter the schedule I go team by team and put a 1 in the "my score" column and make sure that I get a win added to my talley against that opponent. Every now and then it won't update and I'll see that I mispelled that team.

Top/left I have my career record and my record @ each school. The career records @ each school will need to be manually edited. This is just simply a SUM of the W/L columns (columns W and X are normally hidden) for the years that I was there. IE. I started @ UMass in 2014 (which is row 54) and left after 2015 (row 97) so my UMass SUM is "SUM(W54:W100)"

I use an 8 team playoff so I have CCG, Quarters, Semis, Finals for every year. If I don't make the playoffs, then I change "Quarters" to "Bowl." If you don't use a playoff, you can change it to have fewer games and just copy/paste it all the way down.

I think that's about it for the schedule tab (this tab does the most heavy lifting by far and requires the most detail).

Dynasty Info




There's not much to say here as everything here is manually added. I think it's all pretty self explanatory. I use the red boxes for general notes (IE. record breakers, bad injuries, conference realignment, etc).

@ the top there's a box where I track the yearly conference and national champs. below this is where I track my coordinators and their records as HCs @ other schools.

House Rules

Just a list of the house rules I use and where I track some of them like my QB AWR edits.

It's also a worksheet where I do my playoff shuffling every year.

Recruiting


Each season's recruits is manually added on the left. Here are a few notes:

• For JUCOs, enter "SO" or "JR" for whichever year they are and it'll color code them to differentiate them for you.
• Pipeline states are highlighted in orange. The spreadsheet figures this out by simply coloring it orange if there's a space. So, for your pipleines, end the state w/ a space (IE. "MI" is NOT going to show as a pipeline, but "MI " will).
• I enter each recruit as I sign them. Then after the class is finished I do a sort by "Prestige" high to low and then sort "OVR" high to low. This is just my preference.
• The toal # signed is tallied for you and is simply a COUNT of the HGT column.
• The bottom gets your average OVR and Prestige for you.

The upper left is my recruiting history and is mostly manual with a few exceptions. Every year, manually add your TEAM, Prestige, RANK (recruiting class rank), and the # of 5* - 1* you signed.
Every year, I copy/paste my last recruiting class, delete all of the players, and search/replace the old year w/ the new year. The "Total" column is simply a SUM of your 5*-1* that you manually added. The OVR AVG column is equal to the average for each year. IE. in 2015, the OVR AVG (K5) = U40. Every season, when I copy/paste the recruits, I set that year's OVR AVG cell to = the new AVG for the new season. (I didn't explain that well, but look @ the "formulas" in cells K4 - K24 and it should be obvious. Each of those was created when that season was the current one).

Pass, Rush, ....., (S)KR, (S)PR

Besides the schedule tab, these tabs are the heart of the spreadsheet and handle all of the stat tracking.

There are 2 types of tabs here, source tabs (IE. (S)Pass), and the linked tabs (IE. Pass).
Those w/ (S) in front are the source tabs. It says this on every source tab, but to reitterate:
NEVER, EVER
SORT THESE
SHEETS!!!!!!!
FILTERING IS
FINE.

If you sort them, it'll FUBAR all of your stat tracking.

There is a template @ the bottom of each of these (S)ource tabs. For every player on your team (when you start) copy/paste this template. IE. if you have 3 QBs, then copy/paste the TEMPLATE 3 times in the Pass tab.
Then you do a search/replace on each template to change the name from "FR. TEMPLATE" to your players name. The easiest way to do this is to highlight every "FR. TEMPLATE" for the current player and have your search/replace setup to replace "*" with "Rocket McStudly". "*" is a wildcard and it will replace everything highlighted (or EVERYTHING in the entire tab if nothing is highlighted) with "Rocket McStudly" (so make sure you only highlighted the FR. TEMPLATE cells). Then you highlight the next player's FR. TEMPLATE and all you need to change is the replace string (IE. "BBGun McNoodleArm"). This is helpful for non-Pass tabs as you'll need to change the FR. TEMPLATEs and the POS (position) columns for every player.

Do this for every player when starting a new spreadsheet. Once you're going you'll only have to do this for every new recruit of each season. This is the procedure for each new season going forward:

1. Update the templates. Change the "Game 20xx", "Season 20xx", and "Career 20xx-xx" years. The easiest way is to search/replace them going backwards. IE. if your template goes from 2021-2024, replace all 24s with 25s, 23s with 24s, 22s with 23s, and 21s with 22s. Now your template is for 2022-2025. Just changing 24 to 25 (as opposed to 2024 to 2025 or Game 2024 to Game 2025) takes care of every Game, Season, and Career cells
2. Update your new season's schedule for the current year of the template
3. Copy your template from Player column -> Opponent column (but NOT the POS column)
4. Go to the next tab, and paste the new template (from Player to Opponent) over the old one. Do this for every (S)ource tab
5. Now, for each tab, copy/paste the entire template for each NEW player that'll get those stats. IE. for my spread option offense, the Rush tab includes QBs, HBs, and WRs, the Rec. tab includes HBs, WRs, TEs)
6. Search/replace all of your FR. TEMPLATEs and POS for each new player
7. That takes care of every new player, but you need to update the old ones. Copy your schedule (only the schedule part of the template). Go to the top of the tab and search for your current year. This will take you to your 1st returning player. His "Opponent" section for the current year will be blank. Paste the schedule there and scroll down and paste the schedule for the current year for every returning player.

Once you're comfortable with the process, it only takes about 15 minutes to add every new recruit to your stat tracking.

So that's creating/updating the (S)ource tab info. The actual stats are manually entered every game. The easiest way to do this is filtering the tab (FILTERING IS FINE, BUT NEVER, EVER SORT THE (S)OURCE TABS). Filter the "Game" column to only show the current season's game (IE. "Game 2022"). If you have few players for that stat (IE. Pass, Punt, KR) this is enough. For other stats filter it even further to only show your current opponent. Otherwise there'll be too much to show on 1 screen.

Enter the stats each game that aren't calculated for you (These will usually have "#DIV/0!" when no stats are entered) IE. QB Rating, YPC, etc. Enter the rest of the stats and your season and career totals are tallied for you.

Okay.... that's the (S)ource tabs. The non-source tabs are just copies of the source tabs. You'll need to manually link these every now and then (every season or few seasons or so). This is very simple to do. Simply highlight the entire Header row (IE. for the Pass tab highlight A1 "Player" through R1 "Games"), grab the square in the lower right of this highlighted section, and drag it down as far as you need to include every player in the associated (S)ource tab. These cells are all just copies of the (S)ource tabs. IE. in Pass A1, you can see that it "='(S)Pass'!A1"

Once the non-source tabs are populated you can sort/filter to your hearts content. You can see cool stuff like:
Highest career QBR w/ at least 100 completions


Most career yards after contact:


Most tackles per game in a season:


etc, etc.

I think (hope) that covers pretty much everything. Go ahead and explore the different cells and hopefully things you're unclear about will become clear.

I hope you enjoy it as much as I do, but again, I made this for me and it may not be designed in the most user friendly fashion, but it's designed in just about the most jello1717 friendly fashion (which is REALLY convenient for me, but probably not for you).

[Cheese2121]

Re: Dynasty Tracking Sheet

You are the man Jello. Absolutely fantastic. I really appreciate this.

[FarFromEer]

Re: Dynasty Tracking Sheet

Trying to get myself back into the game as the new real life season is about to start. I started diving into your spreadsheet in hope to be able to add more depth to my own spreadsheets. Just curious, have you ever looked at using the COUNTIFS formula, rather than using all the hidden team specific columns. For example, to count wins against Pittsburgh, in cell M2 you would put:

=COUNTIFS(C:C,"HC",J:J,L2,O:O,"W")

This same formula could be copied down and would change results based on the team name in column L. This would eliminate the need to manage so many hidden columns. Just a thought.

Overall, this thing is awesome! I'm definitely working these ideas into my spreadsheet.

[R1zzo23]

Re: Dynasty Tracking Sheet

I freakin' love Excel as I'm a tech teacher and the CountIf formula would be a great addition instead of working with those hidden columns.


Sent from my iPhone using Tapatalk

[jello1717]

Re: Dynasty Tracking Sheet

Trying to get myself back into the game as the new real life season is about to start. I started diving into your spreadsheet in hope to be able to add more depth to my own spreadsheets. Just curious, have you ever looked at using the COUNTIFS formula, rather than using all the hidden team specific columns. For example, to count wins against Pittsburgh, in cell M2 you would put:

=COUNTIFS(C:C,"HC",J:J,L2,O:O,"W")

This same formula could be copied down and would change results based on the team name in column L. This would eliminate the need to manage so many hidden columns. Just a thought.

Overall, this thing is awesome! I'm definitely working these ideas into my spreadsheet.

I haven't heard of COUNTIFS before. I'm playing with it now and it looks really awesome. Thanx for the tip!

On a funny (sad) note, I was experimenting with it in my current spreadsheet where I'm in season 2022. 1st I used COUNTIFS to count the # of times I played Ohio State and it correctly showed me 13. I then expanded it to show me how many wins I had against Ohio State and it returned "4" and I said to myself, "well that can't be right. It's not counting them all." I checked my tally from my previous formula and, sadly enough, it was correct....

EDIT: After playing around with it some more I'll definitely implement it into my spreadsheet. I'm pretty sure I'll be able to discontinue all of my hidden columns on my schedule tab with these. Thanx a million again.

EDIT2: I always hated my clunky way of summing up my wins @ various schools which was to simply add the wins for the rows in which I was at that school. If my rows weren't correct, then it'd be off. COUNTIFS fixes this completely.

[Arrowhead29]

Re: Dynasty Tracking Sheet

I haven't heard of COUNTIFS before. I'm playing with it now and it looks really awesome. Thanx for the tip!

On a funny (sad) note, I was experimenting with it in my current spreadsheet where I'm in season 2022. 1st I used COUNTIFS to count the # of times I played Ohio State and it correctly showed me 13. I then expanded it to show me how many wins I had against Ohio State and it returned "4" and I said to myself, "well that can't be right. It's not counting them all." I checked my tally from my previous formula and, sadly enough, it was correct....

EDIT: After playing around with it some more I'll definitely implement it into my spreadsheet. I'm pretty sure I'll be able to discontinue all of my hidden columns on my schedule tab with these. Thanx a million again.

Please upload the file when you get it all worked out. I'll be using this for my dynasty once we get the rosters finished.

[FarFromEer]

Re: Dynasty Tracking Sheet

I haven't heard of COUNTIFS before. I'm playing with it now and it looks really awesome. Thanx for the tip!

On a funny (sad) note, I was experimenting with it in my current spreadsheet where I'm in season 2022. 1st I used COUNTIFS to count the # of times I played Ohio State and it correctly showed me 13. I then expanded it to show me how many wins I had against Ohio State and it returned "4" and I said to myself, "well that can't be right. It's not counting them all." I checked my tally from my previous formula and, sadly enough, it was correct....

Glad to help. I was literally just exploring the stat tabs and stat source tabs and thinking the SUMIFS formula could be worked in there so you wouldn't have to have direct cell references. No worries about sorting that way either.

Another thing you could do would be to condense, where you have your career record for Michigan in three cells, you could replace the formula in E18 with:

=COUNTIFS(B:B,"Michigan",C:C,"HC",O:O,"W")&"-"&COUNTIFS(B:B,"Michigan",C:C,"HC",O:O,"L")

This will put "87-22" all in one cell.

While it's not really an issue these days, there have been times in my career where I actually created spreadsheets that would not fully calculate because of having too many formulas. Because of that, I often find myself trying to trim spreadsheets down the best I can.

As for that Ohio State thing, you really need to beating them every time. Buck the #uckies!

[Balrog]

Re: Dynasty Tracking Sheet

So I've been trying this out. I put all of my guys in here and now into my 3rd game. Now I just realized that when i go to the Defense page it only shows about 1/2 of the roster from the source defense page. Anybody know how to fix this?