Error log efficiency
Every quarter during factsheet production, we would inevitably run into issues, whether it was with the build of the template, how it rendered, missing disclosures, or tables rendering when they shouldn’t. Since we ran production in 3 tiers, if we had the time, we would fix the issues in between rendering each tier to avoid the same edits in the next tier. We kept track of these errors in various ways. Sometimes based on inventory code, sometimes on job number, sometimes on Fund name, it changed based on who started the log. When we eventually had to share the error log, there always information missing that the responsible party would inevitably need to correct the issue.
It actually drove me nuts entering the same information over and over again in a basic excel, quarter after quarter, knowing that all the information wouldn’t be entered. There had to be a better way! I am far from an expert Excel user, but I did some research. Enter the V-Lookup. I took the production report that was rendered every quarter, complete with all the information anyone would ever need from the error log to address any issue, as well as a lot of additional information NOT needed for the error log. I first stripped out all the unnecessary info out. I then spent some time figuring out what would be the best identifier to use in the drop down menu and determined that the inventory code would be the easiest identifier to use as it is individual for each factsheet and stayed consistent every quarter. I rearranged the spreadsheet so the inventory code was in the first column (the only way a V-Lookup works). The rearranged table was saved as the reference sheet in the workbook. A second worksheet was added and I created a drop down menu in the first column and recreated the headers in the first row, this became the quarterly error log.
Using the dropdown to select the inventory code in a cell would then automatically populate all the other information needed (Product ID, Fund name, Q-R code, Template, Job Number, Tier, Product Type) in that row. Then any member of the team could manually enter the Error, Any Notes, Owner, and the Status. Now, every error log is consistent, provides the same information, is easy to sort to address issues more efficiently, and see the status of the issue. It was a very useful tool to efficiently capture issues during the busy quarter end production period. After the initial set up, it was easy to update for the next quarter using the new production report.
She isn’t pretty, but man she was a huge time saver for the team!
