In my comment on Marc’s blog, I called attention to my three favorite development rules:
- Design your solution following generally accepted principals of systems development
- Test your solution thoroughly
- Document your solution adequately
Now, I am going to add a fourth rule:
If your solution is even moderately complex, don’t build it in Excel!
Excel has been biting me in the hind parts, all week long. I have written several blog posts about the fact that SharePoint is a much better choice than Excel when building solutions that primarily store and lookup information. My suggestions sometimes fall on deaf ears, but when things go south, the problem-child spreadsheets often end up in my lap. Here are two that landed there this week:
Seek and Ye Shall Find – One powerful feature of Excel is the ability to write formulas that gather data from other parts of your sheet, other tabs, other spreadsheets, and even data sources outside of Excel. The problem is that these formulas work even if the lookup data is out of date. Earlier this week, we suffered a curious error due to the fact that someone left a 2010 version of a lookup list in a spreadsheet that had been updated to 2012. This is precisely why I prefer building these solutions in SharePoint and using External Lists or Data View Web Parts to access back-end data. These are live connections to the data and they don’t require the person using them to push “Refresh All” to make sure the list is populated.
It Should Work – One of the most powerful functions Excel has is vLookup(), the ability to find a value associated with an item by finding the item in one column of a table and retrieving the item of interest in an adjacent column. You specify the place to look, and the offset to use when retrieving. The place to look, is a Range of rows and columns either in the a1..c17 format or a defined Range Name. Unfortunately, when you add rows to the data you are looking things up in, you also have to make sure that the range gets expanded. If you don’t, you have the other error we experienced this week, the item isn’t found. Even more unfortunate is the fact that vLookup() doesn’t return “Item not found” by default, it returns the value associated with the last item it checked, which is the last item in the range. Here again, I find myself longing for a solution where one SharePoint list looking up stuff in another SharePoint list. I just have to point to the right list; I don’t have to define the top, bottom, left and right sides of the list.
I know that spreadsheets were the first “killer app”, and some say that nothing has come along on the PC that can rival them; I disagree. Spreadsheet use is out of control; people trust them to do too much work, and they ignore all the rules while building them. Every really complex spreadsheet that I’ve ever seen, was written while one or more people were “in the zone”. They weren’t really designed; they simply morphed into shape as needed. In addition, those solutions are undocumented or poorly documented, poorly tested and they remain in the same fragile state they were born in. Version control is performed by pressing “Save As” and appending a year, event, or people string to the file name. On top of that, I’d be willing to bet 50% or more of the spreadsheets in use today include at least one formula that has been overwritten with a fixed value.
SharePoint offers us an escape from these traps. Rather than try and force Excel into pseudo-database mode, we have a product that is actually is capable of supporting a solution that requires the power and reliability of a database and the control of a content management platform. On top of that, SharePoint gives us a wide variety of ways to extend solutions through workflows and programming. We can automate, protect, distribute and decentralize functions and features without exposing critical elements to random, unauthorized and undocumented changes. I know that it’s easy to reach for Excel. I know how tempting that row-column blank slate can be, but it’s a trap, pure and simple.