Forum Discussion
Excel external workbook links break across users (using cloud storage)
We use Excel desktop on Windows computers with Microsoft 365. Our office uses Sync.com to synchronize shared client files across multiple employees' computers.
Our current setup:
We have a Master Excel workbook linked to approximately 11 individual client workbooks. We create new workbooks and establish the links twice a year, in June and December.
We create links by opening both workbooks, selecting a cell in the Master, typing =, switching to the individual workbook, clicking the source cell, and pressing Enter. We need linked values to update automatically when both workbooks are open.
Our folder structure is:
- Master: Sync\1 - CLIENT FILES\Equitable Block\Block Name\Renewals\YYYY
- Individual workbooks: Sync\1 - CLIENT FILES\Client Folder\Renewals\YYYY
The files are synchronized through Sync.com, and everyone opens them from shortcuts to the shared Sync folder.
The problem:
Excel stores an absolute local path in the formula. For example:
C:\Users\paige_lnsp4p\Documents\Sync\1 - CLIENT FILES\...\[Individual Workbook.xlsx]Myers Position'!$F$16
Another employee's Sync folder is located at:
C:\Users\chris\Sync\1 - CLIENT FILES\...
As a result, links created by one employee work on that employee's computer but fail on others. Changing the source to the local path on another computer repairs the link there, but that is not a practical permanent solution for our team.
Sync.com support confirmed that its desktop app cannot enforce a consistent local path across different Windows usernames. They suggested investigating relative Excel links.
My questions:
1. Can Excel desktop create and maintain relative external workbook links that work across multiple Windows computers when the shared folder structure is identical but the local root paths differ?
2. Given that the Master and individual workbooks are stored in different branches under Sync\1 - CLIENT FILES, is there a supported way to achieve this without moving all workbooks into one folder?
3. If relative links are not supported for this arrangement, is there another reliable way to maintain these links without requiring each employee to change the source paths?
4. Would VBA or another Excel-based solution be appropriate, or would it introduce further reliability or security issues?
We want to preserve our existing workbook design and continue using desktop Excel. We are looking for a permanent solution that works for all employees, not a process that requires manually repairing links on each computer.
1 Reply
Hi Christy,
What you're seeing matches how Excel stores link paths. Microsoft's support article "Description of workbook link management and storage in Excel" (KB 328440) lists three cases:
Source in the same folder as the Master, or in a folder below it: Excel stores a relative path, so the link works from any root.
Source on the same drive but in a different branch (your setup): Excel stores the path from the root of the drive, so \Users\paige_lnsp4p\Documents\Sync\... is saved in the file and breaks wherever the user name or folder differs.
Source on a different drive: the full path with the drive letter.
So relative links do work, but only when the client workbooks sit in the Master's folder or below it. You don't have to move the client workbooks, though. Two ways to keep your structure:
Option A, move only the Master. Save each Master directly in Sync\1 - CLIENT FILES (the folder that holds all the client folders), with a name that says the block and period. Every client workbook is then in a folder below it, so Excel stores relative paths. Re-point each link once (Data > Edit Links > Change Source, or the Workbook Links pane in newer builds), save, then test on a second computer.
Option B, the same drive letter on every computer. Each user runs one line at sign-in (a .bat file in their Startup folder) that points a drive letter at their own Sync folder, for example:
subst S: "C:\Users\chris\Sync"
For Paige it would be subst S: "C:\Users\paige_lnsp4p\Documents\Sync". Point everyone's shortcuts at S:\1 - CLIENT FILES\... and re-point the links once with both files opened from S:. The stored path then starts at the root of S:, which is the same on every computer. This only holds if everyone always opens the files from S:, never from the C:\Users path.
Either way it's a one-time repair, not a per-computer routine.
On VBA: a Workbook_Open macro could rebuild the paths from ThisWorkbook.Path with ChangeLink, but it means .xlsm files, a macro prompt for everyone, and links that quietly stay broken when someone opens with macros off. I'd try A or B first.
One thing that can mislead while testing: the formula bar always shows a full path built from where the file was opened, even when only a relative path is stored. To test, close the client workbooks on another computer, open the Master and use Update Values in Edit Links.
(Drafted with AI assistance and checked before posting.)
