Linking Excel workbooks – some do’s and don’ts

Linking files is a useful feature in Excel but needs to be used with care.

I recently had to develop a model for a client where data held in a single workbook and used to feed many other workbooks for single users. The files were initially held on a network drive location but were subsequently moved to sharepoint. The obvious solution was to link excel files in some way. However, with Excel some things work better than others. Here are some pointers if you find your self with a similar requirement:

1. Some formulas require both linked files to be opened at the same time (in my case the data file and the user file). In my case, I wanted the user have their own file open but not the source data file. My original plan was to use SUMIFS and COUNTIFS linked to the data file to extract the relevant data but these don’t work unless you have both files open.  Some internet searches revealed that Microsoft states this is ‘by design’. There are other formulas that fall into this category including SUMIF, COUNTIF, COUNTBLANK, OFFSET, INDIRECT and many of the database functions. Therefore, don’t use these  formulas for linking files unless you are happy that both files can always be opened at the same time.
2. Pivot tables linked to a source in another file will work on a network but not when using SharePoint.  Pivot tables work extremely well as a linking device when you have the files on your own hard drive or on a network. You only need to have one of the files open at any point in time.  The drawback is that the pivot table will extract the full data set rather than just an extract (even if you set the pivot table to just display a subset of the data). Thus in my example above, any of the users would who had a little Pivot Table knowledge would be able to see all of the data relating to other users as well as their own.  I also found that Pivot table links do not work when the files are located on SharePoint. Therefore, don’t use these Pivot tables for linking files located if user should not be able to access all of the source data or if the file are located on SharePoint
3. Basic formulas such as SUM (or even simply =[external workbook.xlsx]!P16) will work for linking in all situations that I have tried to use them including SharePoint.  These can be used to restrict the user files by linking to a subset of the overall data (which could possibly have been created using pivot tables) in the master data file. Therefore, my recommendation is to use simple formulas to link files as this provides the safest and most robust to do it.
4. Some other points to note on linked files
   • Keep your links to one or two levels only.  Any more than this creates a risk that the one of the links will fall over and be difficult to find. Long chains of links are something that should be avioded
   • If you are copying a worksheet in from another workbook (whether its your own or another users) then check that its not bringing links with in or that if it is then these links will be manageable and will not form long chains.
   • Links can not only come in the form of formulas. Charts, data validation, hyperlinks and VBA can all create links. These are the sort of things that can crate phantom links which can be very difficult to clear (simply going to edit links and breaking them will not always suffice).  A post on clearing phantom links is a subject in its own right and is for another day!
   • Avoid network drive names in links such as N:\sales folder\file.xls. better to use use UNC names, such as \\myserver\salesfolder\file.xls, instead of drive letters. UNC names can make links easier to update when several people will use a linking workbook, because Excel can update links that use UNC names even if users don’t have a drive letter mapped to the network share.
   • Avoid long paths and file names as these can confuse links
   • Links can only return the first 255 characters in a cell