I kept it simple and built it to the standards of the company that I shoot for. Obviously, you can go crazy customizing this by moving things around and adding whatever you want. References A3, the player’s number and puts it in parentheses. It starts at the left of B3 until it hits a comma. It starts from the right of B3, and goes until it hits a comma. This pulls the player’s first name out of the Lastname,Firstname field. The IfError function says, “if it can’t find that acronym, insert the word player instead.” This looks at the Positions sheet and translates LB to Linebacker. If you don’t know what VLookup is, Google it. IFERROR(VLOOKUP(C3, Positions!A:B, 2, FALSE),”player”) $A$1Ībsolute reference to A1, which is the team’s name The second formula is where the magic happens. The $ makes the cell reference absolute so it doesn’t change when you copy it to other cells. If you want to change the alias, just change the contents of G1. It simply puts the contents of G1 followed by the contents of the A column (number). The first formula that I have written generates the alias (i.e. If you want to learn more about writing formulas, there are tons of other websites who will do a much better job. 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 customize the format or if you prefer to get the roster from a different website, you will have to modify the formula. Load these into Photo Mechanic and enjoy some code replacement bliss. If you are satisfied, simply highlight E and F columns and paste into notepad. With E3 and F3 highlighted, click on the bottom right of the selection, and drag down through all the players. More on that later.) In cell G1, put in what you want the prefix of the alias to be (i.e. We can customize the formulas to fix that. (Of course, this will likely not be the case if you get the rosters from another site. Check to see that the team name is in A1 and starting at row 3 are Number, Name, and Position of the players. If not, you may have done something wrong. Voila! If everything went right, you should have the first player’s name and position. Now go to your new team and paste into E3 and F3. Go to another sheet, highlight, E3 and F3, and copy them. Then, delete the extra columns you don’t need. Simple fix: on the Home ribbon, click Clear>Formats. Click on the top left cell and paste the roster in. Go back to the new sheet you created in Excel. Pull down the roster, by selecting everything from the beginning of team name to right of the last player. I based the spreadsheet on NFL.com’s rosters for standardization purposes, but it can be easily adapted for other sites. Once you have created a new page, you can rename it by double clicking on it.įind the roster. Keep reading for the following steps.Ĭreate a new Sheet in the Workbook by clicking on the New Page button along the tab bar at the bottom. Baltimore Ravens wide receiver Anquan Boldin (81)) for each player. You will end up with a new sheet in this workbook with a custom alias (i.e. Don’t worry you can add other teams in seconds. Download here.īecause I only cover local games, this file contains the Ravens and every home game it will play. If you have any skill at Excel, you can customize this to your heart’s desire.įirst, start by downloading this excel file that I have created. I know you can use StonePix to generate these for you, but I rarely trust the site and this gives you more control. I have created a very simple way to generate code replacement sheets in 10 seconds or less. For those of you who use the Code Replacement feature in Photo Mechanic and shoot football, this should make your life easier.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |