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