You’ve been following the site for a while. You’ve even created a spreadsheet to develop your own points league or SGP rankings. You’ve spent all this time building this spreadsheet but it’s getting to be a bit out of date. Players have been traded, rookies have been called up from the minors…
How do you update things? Do you have to rebuild your spreadsheets from scratch each season?
No way, give me some credit! I’m smarter than that. I design things to be reusable.
In this post I’ll show you how to quickly and easily update the Player ID Map in your spreadsheet so you can get updated MLB teams and have new players available to tie in to your projections.
All we’re really doing here is downloading the new version of the Player ID Map and pasting it on top of our existing Player ID Map already in your ranking file. The key is that you have to be very particular about how you paste the new version in. If you’re not careful you will break all the existing formulas in your spreadsheet that reference the PLAYERIDMAP named table.
|1.||Open your existing rankings spreadsheet, the one in which you want the new Player ID Map information. Save a backup copy of the file, just in case something were to go wrong during this process.
Go to the PLAYERIDMAP tab.
|2.||We will soon be pasting information onto this sheet so it is important to make sure all the data is currently showing.
Click on Excel’s “Data” tab and then click the “Clear” button of the “Sort & Filter” icon grouping.
|3.||Click this link to download an updated copy of the Player ID Map.
Once the download completes, open the file. If Excel is displaying any kind of warning message, enable your ability to edit the file (provided you trust this site).
|4.||Place your mouse in cell A1 of the newly downloaded Player ID Map. Then hit the CTRL + SHIFT + End keys all at once. After you’ve done this release the keys. Then hit SHIFT + the up arrow key.
This set of key strokes should select the entire Player ID Map table and then deselect the “Last Player”.
Now hit CTRL + C to copy the selected data.
|5.||Return to your customized rankings spreadsheet. Select cell A1 with your mouse and then paste the data you just copied over cell A1.
The reasoning behind this specific set of copying and pasting instruction is so that the existing table named “PLAYERIDMAP” in your rankings spreadsheet will not be renamed during this process. If you don’t deselect the “Last Player” before copying, the entire Player ID Map table will be renamed and it will break all existing VLOOKUP formulas you have looking for this information.
Well, kind of. Any new players added to the PLAYERIDMAP will not yet be listed on your “Hitter Ranks” or “Pitcher Ranks” worksheets.
This is where you have a decision to make.
If you have taken notes next to players, entered keeper dollar values, or otherwise “hard entered” information that relates to a specific player, then you manually add the player IDs of “new” players to your “Hitter Ranks” or “Pitcher Ranks” tabs.
For example, simply go to the “Hitter Ranks” tab and type the player’s ID at the very bottom of the first column. When you hit enter the Excel table should grow to add your new player and all the other formulas should automatically copy down (another benefit of using Excel tables!).
If you’re not sure what players were added to the PLAYERIDMAP, you can look on the “CHANGE LOG” tab on the newly downloaded Player ID file to see a brief note of all the players added or updated recently.
I try to put brief descriptions of the players that have been added so you can manually add to your “Hitter Ranks” or “Pitcher Ranks” sheets, if necessary.
|7.||If you have not edited dollar values or added player notes, you can copy and paste the hitter IDs onto the “Hitter Ranks” sheet and the pitcher IDs on to the “Pitcher Ranks” sheet.
To do this, go to the PLAYERIDMAP tab in your spreadsheet and apply a filter to only show hitters. On the “POS” column filter, uncheck the “N/A” (if there are any) and “P” check boxes. This will only display the hitters.
Then select cell the first cell below the header in column A and hit the SHIFT + CTRL + Down Arrow Key. Copy this information and go to your “Hitter Ranks” tab and paste it into the first cell below the header in column A there.
After you do this all the other information on the tab should update immediately.
No go back to the PLAYERIDMAP tab and adjust the filter to only show pitchers and repeat the process by pasting those players onto the “Pitcher Ranks” tab.
Now you’re done!
Have Any Questions?
Please leave a comment on this post.
I have to do this quite frequently to keep all the spreadsheets I maintain for the site up-to-date, but this is probably something you’ll only need to do a few times a year. Maybe after the season ends, to get all the new players I’ve added during the season, late February, to get all the players that have changed teams, and once during the season, if you’re doing in-season rankings.
Want More Tips Like This
Make sure to follow me on Twitter, that’s the best place to hear about new posts and updates at the site.