This past week I needed to retrieve a spreadsheet from a Sharepoint library, add a column to a sheet, and then populate that column based on some data I had in Access. Rather than import the data into Access to make the change and then export back (which could overwrite any formulas, lose comments, etc), my thought was to “link” the Excel file so I could treat it like a table, basically the same thing you can do with SQL Server tables. Linking worked easy enough, read the data just fine, but the linked sheet was not updateable. As far as I can tell it hasn’t worked in a while – feature, bug, who knows? It should work.

Plan B – with limited time and patience to invest – was to paste the keys into a new sheet, add the column to the original sheet,and then write a vlookup formula (the Excel version of an inner join,sort of) to show the ‘Yes’ I needed in the new column. Worked fine.

I don’t enjoy the quirks of software, but they do sometimes present opportunities to rethink the problem or the solution.