Warning – The instructions below are likely only relevant if you are following some of my much older work. The Player ID Map has since been updated to allow much easier updating. If you’re looking for guidance relating to a spreadsheet you’ve built or purchased since 2015, you likely want to be looking here for guidance relating to the Player ID Map.
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.
Warning!
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.
Read carefully!
Step-by-Step Instructions
| Step | Description |
|---|---|
| 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). Now refresh the content to pull in any recently added players. Do this by right-clicking on a cell within the table (somewhere within the blue and white rows of data). Then choose the option to “Refresh.” |
| 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. |
| 6. | That’s it!
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. 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! |
Copy this information and go to your “Hitter Ranks” tab and paste it into the first cell below the header in column A there.
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.
My file did not update accordingly. When i pasted, it messed up all the formulas on the rankings pages and then would not calculate correctly.
Hey Jeff, step #4 above is very important to the process. If you still have your original file, try copying again and see if you get different results. The reason step 4 is important is because we are then not copying the entire table.
Excel doesn’t respond well if you copy Table A and paste it directly over Table B. Then you do get an issue like you’re describing. It disconnects all formulas.
But I had found that if I take all but one row of Table A and paste it into Table B, then Table B remains. Just with the info from Table A.
Or the other way you could try it is to NOT copy the column headers. But just copy the player information and paste that.
If you’re still having the problem, you could try another way that takes a little more work.
1. Start with your existing fileDownload the new player ID mapCopy the new player ID map tab into your existing file.
2. Copy/Move the entire worksheet. Not the data like I was describing above. So you’ll probably now have tables in your file for “PLAYERIDMAP” and “PLAYERIDMAP2” or something like that (click on the Formula tab and then “Name Manager” to see what the new one’s called). But all your formulas will be looking to the first one. We’ll need a way to point them to “PLAYERIDMAP2”.
3. Hit CTRL + H to open the “Replace” window. You can manipulate this to have Excel replace any “PLAYERIDMAP” reference with “PLAYERIDMAP2”.
Hopefully one of these works. If you’re still having an issue, try e-mailing me your file and I can try to swap it out for you.
I was having similar issues with neither method working to update my sheet. However, I think I found the problem – in your updated PLAYERIDMAP table the IDFANGRAPHS column has decided the values wants to be text instead of numbers. To fix it, sort the IDFANGRAPHS column alphabetically A-Z. This should put all the “number” text values at the top and all the “sa####” players at the bottom (since they dont need to be converted back to a number). You can then select multiple cells and convert them at once – the yellow error box appeared for me at the top cell I had selected. Fair warning – this is apparently a labor intensive process for Excel and will take some time. 10 cells took my computer about 2 minutes, so we’ll see how long the other 1500+ take…
Hi Dave, hopefully that approach you tried worked. But you’re right, when I do that it can take a long time for it to cycle through all the players. Alternatively, or for a more long-term approach, make sure you’ve read this: https://www.smartfantasybaseball.com/2015/12/an-important-lesson-and-how-to-resolve-vlookup-errors/
What spreadsheet are you trying to update? Is it a rankings one you’ve created from the site?
Yep, it eventually worked, but took the better part of 6 hours to finish everything. It was indeed a rankings sheet that I had created based off the website – which I fully credit for my 2nd place finish with great keeper options in my 12 team league last year. Great work, and thanks for putting this site together!
Wow! Really sorry about that. And I admire your perseverance.