Making Photo Mechanic Code Replacement Sheets Easy!
Aug 21 2010
For those of you who use the Code Replacement feature in Photo Mechanic and shoot football, this should make your life easier. I have created a very simple way to generate code replacement sheets in 10 seconds or less. I know you can use StonePix to generate these for you, but I rarely trust the site and this gives you more control. If you have any skill at Excel, you can customize this to your heart’s desire.
First, start by downloading this excel file that I have created. Download here.
Because I only cover local games, this file contains the Ravens and every home game it will play. Don’t worry; you can add other teams in seconds. You will end up with a new sheet in this workbook with a custom alias (i.e. p81) and the value (i.e. Baltimore Ravens wide receiver Anquan Boldin (81)) for each player. Keep reading for the following steps.
Step Two:
Create a new Sheet in the Workbook by clicking on the New Page button along the tab bar at the bottom.
Once you have created a new page, you can rename it by double clicking on it.
Step Three:
Find the roster. I based the spreadsheet on NFL.com’s rosters for standardization purposes, but it can be easily adapted for other sites. Pull down the roster, by selecting everything from the beginning of team name to right of the last player. Copy that to your clipboard.
Step Four:
Go back to the new sheet you created in Excel. Click on the top left cell and paste the roster in.
Now, it looks all weird. Simple fix: on the Home ribbon, click Clear>Formats.
Then, delete the extra columns you don’t need.
Step Five:
Now we’re ready to make magic. Go to another sheet, highlight, E3 and F3, and copy them.
Now go to your new team and paste into E3 and F3. Voila! If everything went right, you should have the first player’s name and position. If not, you may have done something wrong. Check to see that the team name is in A1 and starting at row 3 are Number, Name, and Position of the players. (Of course, this will likely not be the case if you get the rosters from another site. We can customize the formulas to fix that. More on that later.) In cell G1, put in what you want the prefix of the alias to be (i.e. “p” for p51).
With E3 and F3 highlighted, click on the bottom right of the selection, and drag down through all the players. Boom.
Step Six:
If you are satisfied, simply highlight E and F columns and paste into notepad. Load these into Photo Mechanic and enjoy some code replacement bliss.
Step Seven (Optional):
If you want to customize the format or if you prefer to get the roster from a different website, you will have to modify the formula. This isn’t an excel formula tutorial, but I will go through the basics of the formulas that I have written so you can understand how the current spreadsheet works. If you want to learn more about writing formulas, there are tons of other websites who will do a much better job.
The first formula that I have written generates the alias (i.e. p51). This one is simple.
It simply puts the contents of G1 followed by the contents of the A column (number). If you want to change the alias, just change the contents of G1. The $ makes the cell reference absolute so it doesn’t change when you copy it to other cells.
The second formula is where the magic happens.
Let me break this down step by step.
| $A$1 | Absolute reference to A1, which is the team’s name |
| IFERROR(VLOOKUP(C3, Positions!A:B, 2, FALSE),”player”) | We’ll start with the VLookup. If you don’t know what VLookup is, Google it. This looks at the Positions sheet and translates LB to Linebacker. The IfError function says, “if it can’t find that acronym, insert the word player instead.” |
| RIGHT(B3,LEN(B3)-FIND(“, “,B3)-1) | This pulls the player’s first name out of the Lastname,Firstname field. It starts from the right of B3, and goes until it hits a comma. |
| LEFT(B3,FIND(“,”,B3)-1) | This gets the player’s last name. It starts at the left of B3 until it hits a comma. |
| “(” & A3 & “)” | References A3, the player’s number and puts it in parentheses. |
Obviously, you can go crazy customizing this by moving things around and adding whatever you want. I kept it simple and built it to the standards of the company that I shoot for. The best part about this is that what used to be a 10 minute tedious process of creating these spreadsheets is now 10 seconds and it only took me a half hour to build the spreadsheet the way I wanted. Hopefully, this can save you time, too.
Email me at maxwell@maxwellkruger.com if you have any questions.