Ok, let me start by saying that the use of the term “hell” is probably a little too strong for the situation I’m about to describe. But I just stumbled across an old post in Smurf on Spreadsheets linking to a paper written by the author, which struck an immediate chord with me. Without reviewing the contents of the paper, I’ll just mention a couple of the points which stood out:
- The “key man” dependency problem – critical spreadsheets and systems of spreadsheets in many corporate environments have been built, or at least designed, principally by one person. This one person hands the system over to the prospective business owner or end user with either little or no documentation, technical or otherwise. No-one else has the same perspective on the overall structure of the system, or sufficient knowledge of its inner workings to be able to support it. So when that person leaves the building, where does that leave the user when something goes wrong? Not in a good place…
- External linking – It’s an extremely common (and risky) practice to link related spreadsheets within a system or application in a rigid, predetermined fashion in order to collate, summarise or manipulate data, slicing and dicing on the way to producing a presentation layer (perhaps a management dashboard or other regular report) at the end. This often means that the accuracy of the results and/or the completion of the process is extremely sensitive to both the structure of the data and to the containing file structure. As a result, an error in the process often means a protracted, tortuous investigation to trace the point at which it originated.
So my situation relates to both of these points. When I started in my current job, about a year ago, I was designated as the support person for a monthly business reporting process in another team. My predecessor had been the person who “designed” this process, which involves a total of about 60-70 spreadsheets in total, 30-odd of which are raw data entered into templates, and the rest summarising this data in various ways, resulting in 3 or 4 key reports for various consumers, mostly upper management.
The issue I have is that the process is, overall, a complete mess. The importing of data and regeneration of consolidated new reports is handled by a bunch of VBA routines, which is why I was nominated as the support person (Geoff knows VB, he can do that). Imagine my horror to discover that the ongoing maintenance of the code required, each and every month, copying and pasting every single routine used the previous month and then replacing within it every single reference to that month – month names, dates, file paths, everything. There were no variables used in the code, anywhere – it had basically all been recorded, then updated, every month for the last three years. Anecdotal evidence suggested the bloke who was doing this spent around 3-4 days every month on this and a couple of other similar processes.
Well, I was having none of that, so I spent around 3 weeks painstakingly untangling the intent of the code, and then refactoring, until finally I was left with something that requires no regular maintenance. This now leaves the real problem to deal with, which does require regular attention.
Most of the cells in the monthly summaries represent the last link in a chain of dependency stretching back over several workbooks. It works, mostly – but where it doesn’t, leaving incorrect or #REF values, it is a real pain in the arse finding where it’s gone wrong. The person who runs the reports calls me up most months to report just that happening, and investigation often reveals nothing more than links not being updated. Or occasionally I’ll discover a link pointing to the wrong place, or a template incorrectly filled out. What the process needs is essentially complete redesign, but given the current constraints on budget in our business, it’s not going to happen anytime soon. In fact, I can see this stretching out over the next 5-10 years, and when I’ve gone, the next person to support it will be in much the same situation I was. I’ve made some attempts at documentation, but I really don’t have the time now for anything else.