Maintaining/restoring links to other Excel worksheets when a "master" excel workbook is moved from local drive to One Drive and back to local drive

Use Excel’s Change source command to redirect every formula referencing an old source workbook at once, rather than repairing links worksheet by worksheet.

Relink each source workbook

1. Make a backup copy of the master workbook before changing its links.
2. Confirm that every source workbook is in its intended permanent location.
3. Open the master workbook in the Excel desktop application.
4. Select Data → Queries and Connections → Workbook Links.
5. For a broken source, select More Commands (…) → Change source.
6. Select Browse, locate the corresponding source workbook, and choose it.
7. Repeat for each distinct source workbook listed in the pane.
8. Select Refresh all, then save, close, and reopen the master workbook.
9. Return to Workbook Links and verify that the sources refresh successfully. You can use More Commands (…) → Open workbook to test each repaired source.

Change source updates all references to that source workbook throughout the master workbook, so it is not necessary to edit every linked cell or worksheet individually.^1^

If your Excel build shows the older interface, use Data → Edit Links, select the source, and choose Change Source. Do not choose Break Link: that permanently replaces linked formulas with their current values and cannot be undone.^1^

If some links are not listed

Excel links can also exist in defined names, charts, shapes, and other objects. To locate formula-based links:

1. Press Ctrl+F and select Options.
2. Enter *.xl* in Find what.
3. Set Within to Workbook and Look in to Formulas.
4. Select Find All.

Also check Formulas → Name Manager and inspect the Refers To column for references containing another workbook name. Excel does not provide one automatic search that finds every possible type of workbook link.^1^

Prevent the links from breaking again

Excel uses relative paths whenever possible. Those links survive a move only when the master and source workbooks retain the same positions relative to one another. For example:

ExcelProject\
    Master.xlsx
    Sources\
        Input1.xlsx
        Input2.xlsx

Move or copy the entire ExcelProject folder between the local drive and OneDrive instead of moving only Master.xlsx. Keeping Sources beneath the master’s folder with the same names and hierarchy allows relative links such as Sources\Input1.xlsx to remain valid.^2^

If the source workbooks remain local while only the master moves to OneDrive—or the files are moved between different drives—Excel may store or resolve absolute paths instead. In that arrangement, moving the master back does not necessarily restore the former paths. After repairing the links, keep the whole workbook set together in one stable folder tree wherever practical.^2^

References

1. Windows
2. Description of workbook link management and storage in Excel