When two Excel workbooks are linked, or referenced, selected cell data in the Source workbook appears in referenced cells in the Destination workbook. If data is then updated in the Source workbook, the linked cell in the Destination workbook reflects the change. This was nice and simple when files were stored locally (or on a shared network drive), but with the wider use of OneDrive things have become a little more complicated as links created on one PC – if you open the files from within Excel – will be broken if the same files are opened on another.
The overall solution is to first open both the Source and Destination workbooks via the OneDrive web portal, and not via Excel itself:
- Login to OneDrive via the web portal
- Browse to where your Source file is stored
- Click the three dots (ellipsis) to the right of the file name
- Select Open and Open with Excel. The file should now open in your locally installed Excel version.
- Repeat the steps above for the Destination workbook.
Once both workbooks are open:
- Select the cell in the Destination sheet as normal and enter the = symbol
- Without clicking anything else, switch to the source workbook and select the cell that contains the data you want to reference, followed by hitting Enter.
You will then be taken back to your Destination workbook with the link to the Source cell entered into the originally selected cell. Now if the document is opened by someone else the links to the Source workbook will correctly update.