Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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.
Amen!
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!! 🙂
Check out Power Query (Get & Transform in Excel 2016) this can simplify budgets by providing an easy way to consolidate many Excel files into a single data table.
As an example you could save copies of all the various files into a single folder and update the Power Query and it will have all the latest data from the excel files in that folder. Replace a single file with new data and then refresh and the data table is updated.
Where ever possible get your data into tables and work with the tables.
Since 2016 I've been using Power Query for linking and sometimes I use VBA where necessary to refresh data etc. The old model lining through Formula is really troublesome. Thank for posting this.
Hi, I have been trying to develop a Design table template linking a Excel file to my model. The spreadsheet has multiple tabs having data, formulas, and all calculations stored in them, "Sheet 1" has data for solidworks which are derived from these other tabs.
The problem is the external file does not update the model if there is any change, have to open the excel file and edit a cell, before any update takes place. Placing the sheet within solidworks as a design table removes all updating problems. Any remedy?
Hi, would using VBA to copy data from master to child workbook be effective or would it be slow
A VBA solution assumes macros are enabled and /or allowed - not something you can easily control. If you can make sure macros are enable then it may help, but Power Query is a better solution.
Hi,
I have a workbook with raw data and an excel workbook linked to that. Due to the structure of our system they need to be separate. The problem I am having is that links to not update automatically or even upon clicking "update values". After clicking "update values" Excel opens Explorer and I have to choose the correct file. I am using mapped drives and the two files are on separate drives in our system but the problem even occurs when I have them on the same drive but different folders about 2 relative steps away from each other.