If your only hammer

There’s a saying in IT circles that “if your only hammer is Microsoft Office, it’s amazing what looks like a nail”.  And it’s quite apt, lots of technologists get irritated seeing people struggling to do things because they’re using the wrong software.  But I think there’s more to it, and I propose an update to the proverb – “if your only software is Microsoft Excel, it’s amazing what looks like a spreadsheet”.

The problem with the way some people use Excel is that they think it’s a database.  I was asked by one of the admin staff at work for some help identifying rows on a spreadsheet in which one of the columns values also appeared in a second worksheet.

To me Excel’s VLOOKUP function is a pain in the arse.  To use that, you’re almost certainly dealing with relational data.  So why not do the job properly and use a database, where you can craft an SQL query and get much more flexibility?

Until very recently I had a severe dislike of Access, but I’ve found a great feature in it – Linked Tables.  These turn out to be fantastic for getting out of the kind of problems where some well meaning but misguided person has used Excel when they neded a database.  You can use their Excel mess as a table or tables in your database.

Using Linked tables means that you can continue to update the Excel file and the queries will have access to the updated information.  This came in very handy with a ridiculous system where a friend of mine was having to enter some information onto a spreadsheet from a pile of paperwork, then trawl through it to create a seperate report which aggregates the data into a different format.  Because it had always been done that way, naturally.  Of course because both reports were compiled manually there were discrepancies and a lot of stress.  By adding some extra fields to the  spreadsheet outside the printable area, it could be linked to a database and the second report generated automatically, with no possibility of the two reports having totals which disagreed.