National Pokemon Association Spreadsheets
National Pokemon Association Spreadsheets
The National Pokemon Association is an annual tournament run by the Pokemon VGC community. The tournament usually consists of 10-16 teams, each of which play 8-11 weeks of regular season play followed by a single-elimination postseason. Each team usually has one or two managers, and before each season these managers draft players onto their teams. Earlier years used a snake draft format, but an auction format was adopted during NPA 3 and has been used since. A number of the league's playoff tiebreakers are determined by each team's statistics, making keeping track of them accurately and making them comprehensible and visible for all to see important.
All spreadsheets: NPA 2 | NPA 3 | NPA 4 | NPA 5 | NPA 6 | NPA 7 | NPA 8 | NPA 9 | NPA X
In 2013, during the second season of the National Pokemon Association (NPA) tournament run on Nugget Bridge, I had noticed there was a lot of data to keep track of, but nothing had been set up to organize it. I didn't have any prior knowledge on spreadsheets other than the basics I learned in computer classes in high school, but I gave it a try and went about setting up a sheet for the league. The first rendition was pretty bare-bones; it didn't have any significant automation, but it managed to collect and display the necessary information for the league effectively, and looked presentable to boot.
For NPA 3 the following year I re-designed the sheet, and Ben Kyriakou offered to help write the sheet's code after the design was finalized. He fully automated the sheet using VLOOKUP to gather data and RANK.EQ for sorting the standings, making updating each week's results a matter of simply editing the schedule and entering match scores. This significantly cut down the time needed each week to update the sheet and reduced the opportunities for errors to occur when doing so. The NPA 3 spreadsheet set the foundation for the next several years, as even when trying to improve upon the sheet, it proved unnecessary or impossible to do so because of how well VLOOKUP worked for helping to collect, organize and automate this type of information.
For NPA 4 I kept things largely the same with only a few tweaks to the sheet design, namely the font and the look of the standings page. The draft page was added, making it easier for teams to review the league's selections (and as it turned out, easier for spectators to discuss which teams were the best pre-season.) I received help from Guan Yang Ze (Level 51) in transferring the code from the other sheet, and also received help from him with data entry as the season went on.
NPA 5 was the first season in which I didn't re-design the spreadsheet. Yang Ze helped again with the code, and Mike Suleski (OmegaDonut) helped clean up things with the code during the season as well.
The NPA 6 sheet wasn't coded. I had a lot of time on my hands to update the sheet and not a lot of time before the season began. Jen Badamo did a lot of the updating as well, and this went much more smoothly than I had expected it to. I did learn that it was generally a better idea time-wise to write the code in the future though, even if the season had already begun, as it would have saved significantly more time than it took. The design for the sheet was tweaked slightly on the team cards - the size was compressed a bit and more readable data was added to the results area, but that was a result of not coding the sheet, and I deduced that it wasn't worth it as I got virtually no feedback, positive or negative, on the team card design.
I made a new design from scratch with significant changes for NPA 7. I added logos for each team on the standings page and team cards, and I revamped the team cards, creating a frame around each and changing the layout. I also changed the font of the entire sheet to better match the new layout. The more neatly organized team cards allowed me to add additional data for each team's game record, team awards, and assistant manager. I coded and managed the sheet entirely on my own for NPA 7, something I had wanted to attempt before doing so and decided would not happen again afterward. It was something I could handle on its own, but as I had other duties with the league (I was both commissioner and a player that season), it was ultimately a little too much for one person to juggle.
NPA 8 was the first NPA in which I did not primarily run the spreadsheet, although I did help with coding and data entry. Commissioner Ryan Tan took the reigns and fellow commissioners Jason Krell and Chalkey Horenstein also helped with data entry and pointing out necessary tweaks. The design from NPA 7 was re-used with only changes to the color scheme.
Before NPA 9 I decided to once again overhaul the design. I wanted to try a design a little less bold with a little more finesse. Ultimately it ended up turning out close to how I wanted it to be, as the sheet looked crisp, save for a few small details.
The other reason I wanted to overhaul the design was because I wanted to make the schedule code easier to replicate each following season. Having separate sheets for each week allowed for the sheet's VLOOKUP code to be more predictable. When the entire schedule is on one sheet, all of the row locations change week to week, and none of the positions are memorable, which makes it necessary to switch back to the tab often while writing each team card's code. Having a separate, uniform sheet for each schedule made it easy to memorize the position to target with VLOOKUP in the code.
This change also improved user interaction and made it easier for the sheet admin to update, as instead of each week being further buried within the one-sheet schedule, anyone could view a single week without having to scroll down. This also allowed the current week(s) to be dragged to the left of the sheet menu, making it easier to display relevant information. Gone were the days of needing to rearrange the schedule page every week to bring the most recent matches to the top, or as often happened, ignoring that task completely and dealing with poor UI.
As a result of these changes, I felt more comfortable allowing more people to help update and collaborate on the spreadsheet. My goal was to be able to pass this along with ease once I was no longer interested in working with NPA, and I felt this sheet accomplished a very important step toward doing that.
I decided that NPA X would be the last time I was going to design an NPA spreadsheet. My interest in the game was waning, but I did want to do one last sheet and put everything I had learned into it, ironing out every little detail along the way. The base for the code was almost entirely set due to the changes I made to the sheet for NPA 9, so I was able to invest most of my time on building the sheet as perfectly as I could. The team cards from the previous season were kept the same as I really liked that design, and I used that design as a reference point and cornerstone for the rest of the sheet.
One of the first and most important changes that I wanted to make was to make the sheet in dark mode. I tried to do it without including any sort of grid reference at first, but this turned out to be a mistake as it made it a little more difficult to read and a lot harder to figure out how to format borders without making it difficult to sort or replicate sheets. I ended up adding dotted lines and making the entire sheet both dark mode and grid-like, and it ended up making the features of each sheet really pop out of the background, something I had not been able to accomplish in previous renditions of the sheet.
Another focus I had was making the sheet readable, as uniform as possible, and as colorful and interesting as possible while not being overdone. Adding logos wherever I reasonably could along with adding the NPA logo to the standings page greatly helped with this. I also made the colors for each team soft enough that I could use black font, as I didn't want something like the previous season's white Hams font again.
Like in NPA 9, I welcomed a bunch of help updating the sheet, and tried to explain things to others who asked about how the sheet worked. NPA 11 began without a hitch spreadsheet-wise, and apparently the sheet was able to be re-used without me being involved, which I consider a huge success and exactly what I set out to do in the final two years of building the sheet. My hope is that it can continue to be used and easily tweaked for as long as NPA is running.
Season by season changes to the standings page