Linking spreadsheets: don’t do it

Spreadsheets are linked when formulae in one spreadsheet reference cells in another. There are two circumstances in which spreadsheets become linked: consciously and unconsciously.

First, let's deal with conscious linking.

My general rule of thumb on this subject is that spreadsheets should only be linked if two conditions both hold true:

  • The location of the spreadsheets must be static. There must be no emailing them around the place, or moving them from drive to drive; folder to folder
  • There must be an overwhelming reason for the data in the two spreadsheets to be kept separate.

If I do a piece of work for you, I will never link spreadsheets. And I mean never. The ensuing pain just isn't worth it. You see, when the linked spreadsheets are created, everything's dandy. Spreadsheet 1 references some cells in spreadsheet 2. They're both located in the same folder, and everything sings.

But one day, one of the spreadsheets will be moved to a different folder. Or a column that's of vital importance will be deleted.

When you delete stuff that's vital from within a single spreadsheet, it's usually quite easy to spot that it's caused some errors. You'll see a bunch of #REF! values that you can go away and fix.

But when the spreadsheet that's broken isn't even open, then you're in trouble. You delete some stuff from spreadsheet 2 and save it. The following week, you might open spreadsheet 1, and you're suddenly confronted with a bunch of #REF! values. And you have no idea why.

Now for unconscious linking. This happens when you cut or copy stuff out of one spreadsheet and plonk it into another. You might have an entire sheet that you want to hive off, or you may simply cut a chunk of data from a sheet into another workbook.

If that range of data contains any formulae that reference cells outside of that range, then if you click on that cell in its new home, instead of saying:

=B1

your formula will read:

=[Spreadsheet2]Sheet1!B1

And suddenly, you have a problem. As soon as you change Spreadsheet 2, there's a chance that Spreadsheet 1 will break.

So my advice is: kids, don't do it. It's just not worth it.

This entry was posted in Advice. Bookmark the permalink.

2 Responses to Linking spreadsheets: don’t do it

  1. Jeff Weir says:

    Amen!

  2. Kieran says:

    After reading your guidance above I am hoping you can provide some further direction that can help me with my issue re: linking spreadsheets.

    I have a need to copy data from one workbook to another (that are in different folders) which I feel fits into both conscious and unconscious linking.

    The reasoning is that my business has multiple workbooks and spreadsheets with FY16 budget data for multiple business units but my monthly reporting is done in one workbook.

    Prior to me doing this role the monthly reporting was very laborious as the budget data was inputted manually from the source data into the destination sheets. Therefore as the source data was static, with no option of being corrupted, I felt that linking/copying certain pieces of data the worksheets was the only option.

    I have started doing this but am now facing issues with the source files as they are saved on my company's network and when trying to open them it says that I have them 'locked for editing' even though they are not open.

    I assume this is to do with the linking.

    Please help!! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *