How To Cross Reference And Link Cells Between Different Excel Files
Linking two excel sheets so that one can read a value from the other is much easier than it sounds and it’s a function that a lot of people need. No one has just one Excel file and while it is easy to add data from file to the other, keeping up with the changes so that they are reflected across all files is both time consuming and prone to error. The following little tutorial helps you link a cell from one Excel file to a cell in a different Excel file such that it updates itself.
You will first need both your files open. Identify which cells you want to link. For the sake of this tutorial, we will assume you have two files called A and B. Worksheets are immaterial here. You want to reference cell A4 in file B so that cell C2 reflects its value.
Go to file A, select cell A4 and copy it. Next, open file B and select cell C2. On the Home ribbon click the arrow at the bottom of the Paste button and you will see a drop-down arrow. Click it and select the ‘Paste Link’ option under the ‘Other Paste Options’ section.
Each time you change the value in the source file, i.e. in file A, it will update automatically in file B when you launch it. The link will work so long as the the file’s location remains unchanged. The files do not have to be located in the same folder.
I know this is a old question, but is there a way to link the cells to allow for either one to update each other?
If you’ve got both XLS open, you can also use it for formulas automatically.
e.g. if want to do a VLOOKUP across workbooks, when it prompts you to select the range to query, you can just highlight it in the other workbook, and it’ll auto-fill in the workbook location & Sheet prefix of the range.
The main gotcha though (and why you need to be careful), is that Excel will cache the last value it can read.
i.e. if somebody deletes/moves/renames the source XLS, you’ll get a warning the source has vanished, but your target Excel will just persist whatever it last saw.