How to link external files into an Excel file and then change if required
A common requirement is to pull data from one Excel file into another. You should begin by setting the scene. i.e. make sure you have all the files you need open. Let’s call those source files.
Our source files are:
Then navigate to the file where you are pulling in the data. Let’s call that the Destination file
- In the Destination file, click in the cell you want the answer to appear in. In this file the instructions have already been set up.
- Navigate to D7 in the Destination file and press =
- Then navigate to the cell in your Source file you want to pull in e.g. in Galway – January and click on E7 and press Enter.
- Note that your formula will show the full file name, sheet name and will lock the cell ($$) so you will need to unlock that first – by pressing F4 until the $ go – assuming you want to copy down your formula
How to change the link
For this you will need to use Data | Edit links and then – making sure you click on the file path you want to change – choose Change Source. This will allow you to navigate to the desired replacement.
You can view the video here