Links to external workbooks

I have never been a fan of linked workbooks, here are some of the reasons:

  1. You can’t easily be sure that every workbook in the linked chain was updated in the right order
  2. Excel can’t warn you of circular references through closed workbooks
  3. Workbook links can lock you into a specific file and folder structure
  4. People never seem sure whether they should update links when asked on opening or not – more confusion.
  5. If the source file is not found, error messages can be confusing
  6. If the source workbook changes (eg a row gets inserted) your linked formulas might end up pointing to the wrong place, and returning incorrect data.
  7. in certain circumstances you get to pick the replacement file for every linked formula (could be 100’s of times) – this sometimes leads to crashes.
  8. Changing file and path names, or locations,  can burst formula limits, making them uneditable
  9. They just seem to be a bit too fragile and unauditable.
  10. There is rarely an agreement as to what the soure data was created for, what it is appropriate for, and how stable it is.
  11. Links are 1 way – there is no (easy) way to know who has linked to your workbook – this is a big danger if you need to make significant changes
  12. they seem to rapidly degenerate into a rats nest of links, half of which always end in ‘book1’ or file not found. (I’ve seen over 100 links feeding one workbook, directly and indirectly)
  13. You can’t stop them (assuming people can read your data, they can link to it)
  14. the big one – see below.

The big reason why I don’t like links finally became clear recently:

I don’t like linked workbooks because it indicates a lack of investment in proper information systems, or training in existing systems.

I have found that such a lack of investment shows through in many places, in organisations that have lots of linked workbooks. Likewise spreadsheet quality tends to be low and neglected in such organisations. General understanding about basic development, design and risk is low to none too.

My preference is use Excel as a thin presentation and analysis layer for information workers to interact with corporate information in a meaningful way. To do that the spreadsheets these analysts use really need to link to reliable, flexible data stores. Well built relational databases usually fit this bill, as do Essbase databases and Analysis Services cubes. Half finished Excel workings do not. And lets be honest, very few Excel workings ever reach ‘finished’ (if you define that as tested, proven and assured).

I still occasionally use links if I have to, but generally I prefer to use a VBA import routine with date, time and userID stamps. But even that is making the best of a bad job, what is really needed is a commitment to information that is fit for purpose, and that means direcly from a reliable source.

Anyone got some more bad things to say about links that I missed? anyone want to defend them? Am I reading too much into this?

I think my favourite data source currently is Essbase, (I havent used AS for a while), whats your favorite?

cheers

Simon

Advertisements

11 Responses to “Links to external workbooks”

  1. Marcus Says:

    Agree with all of the above and…
    – Inevitably in the labyrinth there’s at least one link which points to someone’s C: drive. refresh that.
    – the frustration of phantom links (particularly the less obvious ones).
    – Accuracy – if I don’t update these links, will the spreadsheet be accurate. But if I do update these links…

    Also agree with your “lack of investment in proper information systems” view. Too often I’ve witnessed basic data (simple example: postal codes) duplicated across various spreadsheets or databases which are then linked to. There is no process to manage the accuracy of those sources, nor any plans to consolidate them into one version of the truth.

    I haven’t touched Essbase since about version 5.5 or 6 – at any rate it still used its proprietary query language Report Script (I believe they’ve gotten on the MDX wagon since). More recent has been MSAS. I’ve been dabbling in OLAP solutions for some years I’m somewhat disillusioned by them.

    Most reporting requirements are reasonably predictable (enter the Pareto principle) – too often I’ve seen huge sums invested in reporting cubes (including infrastructure) when some well indexed, pre-aggregated RDBMS would suffice (Teradata for example).

    The majority of report consumers are not ‘analysts’ in the true sense – they want predefined reports with the capacity to play with some of the numbers or reformat graphs. Those who slice’n’dice and utilise OLAP’s analytical capabilities are few and far between (or is that just me?).

    For small projects and prototyping I use Access. For larger MSDE or SQL Server.

    Cheers – Marcus

  2. tom Says:

    For all but the simplest of environments a back-end store is essential, in the past I would have used Access, Oracle or Essbase. Now I would mainly use free tools such as OracleXE or SQLite (great for large in memory databases, requires use of VBA and a COM addin).

    I’m also starting to evaluate the free Palo OLAP tool from German company Jedox (www.palo.net) as a cost effective alternative to Essbase. As an alternative to linked spreadsheets, Web Queries against an HTML source (using perhaps a tool such as RSSBus (www.rssbus.com) to convert the data source to an HTML table) are worth considering.

    Tom

  3. Bob Phillips Says:

    Don’t forget that dependents/precedents can’t be traced acroos worksheets, never mind workbooks.

  4. Bob Phillips Says:

    Personally, I couldn’t disagree with anything that you have said. They are second only to shared workbooks in my pet peeves.

    Another point against, albeit not as major as some that you have mentioned, is that dependencies/precedents don’t show across worksheets, never mind workbooks.

  5. Dennis Wallentin Says:

    I don’t like links.
    I don’t like links.
    I don’t like links.

    >>My preference is use Excel as a thin presentation and analysis layer for
    >>information workers to interact with corporate information in a >>meaningful way.

    Exactly my preference :)

    The major clients either use the RBDMS vendor’s toolkit or use a corporate developed toosl. Except for that Marcus statement of Pareto is close to my experience.

    Kind regards,
    Dennis

  6. Charlie Hall Says:

    Hi,

    I agree that links are dangerous – here are my additions to your list

    Links to files in the same folder or subfolders are stored with a relative path to the main folder – links up to the folder above or over and down – are stored with an absolute path – very confusing for average users – as it makes moving/copying a folder of linked spreadsheets unpredictable to many

    Links can be hidden in range names – not obvious why a link does not go away when all in cell references are removed

    Some links in range names do not show up in the link table – still confusing me why this is so – but have not investigated it yet

    –Charlie

  7. Will Riley Says:

    Aaarghh…

    Don’t get me started on links! Grrr!

    Excellent article as usual Simon and I am in the “I hate links club”.

    As for data sources, I’m actually really enjoying the interaction between Excel & SQL 2005 (database engine & Analysis services). The 2003 Excel AS addin is a bit clunky (I guess it was hacked together a bit) but the 2007 stuff is lovely to work with.

    Shame we have to wait for SQL 2005 SP2 to get it all working properly in MOSS 2007!

    Regards,

    Will

  8. Simon Murphy Says:

    Glad I’m not alone in the anti-link club.
    I had forgotten the dreaded ‘C:\’ link that as you say Marcus is always there. (bit like a Module1 in VBA)
    Thanks for the alternatives Tom, its so easy to get stuck with what you know, I have seen the Palo links on DDOE, its now on my list of things to investigate. (Let us know how you get on).
    Thanks for the SQL update Will, I must try out the 2003 add-in, all my stuff has been through ADOMD.
    cheers
    Simon

  9. Simon Murphy Says:

    Cheers for the input Bob and Charlie.
    Bob I never use shared workbooks, tried them once, didn’t work right, I’m guessing from your comment they are still well worth avoiding!
    Good point on inter sheet deps/precs, the only vba way (that I know of ) is to navigate the tracer arrows, which strikes me as flaky at best.
    Charlie, I’m not the worlds biggest names fan anyway, but sticking links and other clever stuff in there makes things even harder to test. Good point to about the absolute/relative paths – a bit of consistency would be handy indeed.
    cheers
    simon

  10. Simon Herbert Says:

    I first discovered how much I hate links when I inherited loads of files that contained them – along with a specific set of notes telling me which files should be opened in which order (at least I had notes…).

    Most have these have now been replaced, but I still come across the odd one every now and then (and often open them in the wrong order).

    As for shared workbooks – apart from the ability to track changes are there any benefits to using them?
    I have never seen this feature used with any success (more often than not I have seen corupted files and frustrated workers). I think its a lot better to set a workbook to read only recommended and teach people to only go in to edit it when they actually need to. Most of the time this works, but there is always the notify option if someone is blocking the file (as long as you don’t exit Excel).

    I think that shared workbooks and alternatives to using them could be a topic on its own…

  11. Bob Phillips Says:

    Sorry I posted twice, when I didn’t I didn’t see the results.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: