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:
your formula will read:
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.