OmniDocFix Excel Formulas That Break Between Sheets and Workbooks
Diagnose why Excel formulas fail across sheets or linked workbooks, match the symptom to its cause, and repair the reference step by step.
Your Excel formula returns #REF!, shows zero, or keeps displaying an old number after you point it at another tab or a second file. It worked a few minutes ago, or it works on a colleague's computer, so the formula itself looks fine. The break is usually in the reference: a sheet name, a file path, or the state of the source workbook.
This guide is for anyone whose formulas fail when they reach across sheets inside one workbook or link to a separate workbook. Cross-file links are a common source of confusion, because Excel shows a path you may not recognize and refreshes values only under certain conditions.
You will learn how to read the reference, match the symptom to its cause, and repair the link. The steps apply to standard Excel (.xlsx) files, including files you open from a shared folder.
Why do Excel formulas stop working between sheets and workbooks? The short answer
Usually the reference no longer points to the right place, or Excel cannot read the source value at the moment it calculates. Match your situation to the row below, then follow the fix.
| Your situation | What to do | Why |
|---|---|---|
| Formula returns #REF! after a tab was renamed or deleted | Retype the reference with the current sheet name, or restore the tab | Excel cannot find the sheet named in the formula, so it returns #REF! |
| Formula shows a full path and old numbers after you closed the source file | Open the source workbook, then accept the link update | Excel keeps the last stored value from a closed file and refreshes it only when the source is available |
| Workbook was moved, renamed, or sent to someone else | Repoint the link to the file's new location using Excel's list of external links | The stored path points to the old folder, so the link breaks even though the file is intact |
| Total across sheets returns zero, but the source numbers look right | Convert the text-formatted numbers in the source range | Excel adds only true numbers, so text values are skipped |
| Values never change after you edit the source | Turn on automatic calculation or press F9 to recalculate | The workbook is set to manual calculation, so formulas wait for a command |
Figure out which problem you have
Read the formula bar before you change anything. The symptom tells you where to look, and the table below links each one to a likely cause and a quick check.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| #REF! error | Sheet or file name typed wrong, or the target was deleted | Compare each name in the formula with the tab names and file name, letter by letter |
| Formula contains a file name in square brackets with a folder path | The cell pulls data from an external workbook | Look at the formula bar; a bracketed file name means an external link |
| Old value that does not change | Manual calculation, or the link was never refreshed | Press F9; if the value updates, calculation was the cause |
| Zero or blank even though the source has data | Numbers stored as text, or the source cell is empty | Check whether the source value is left-aligned; text numbers often are |
| Security warning about links, and values stay stale | Links are blocked, or the file opened in a restricted view | Enable editing for a file you trust, then look for a prompt about updating links |
| Works for you but fails for a colleague | Different folder, drive, or file location | Compare the full path shown in the formula bar on each computer |
Fix it step by step
Start with the easiest checks. Each step narrows the problem before you move to link repairs.
- Read the reference exactly. Click the cell and check the sheet name and file name. A sheet name with spaces needs single quotes, such as ='Q1 Sales'!B4. A file name in brackets must match the workbook's name exactly.
- Confirm the sheet still exists. Look at the tab names in the source workbook. If a tab was renamed or deleted, restore it or retype the formula with the current name.
- Open the source workbook. Excel refreshes values from a linked file when that file is open or when you choose to update links. Open the source, then accept the update prompt only if you trust the file.
- Repoint a moved link. Open Excel's list of external links for the workbook, select the broken source, and point it to the file's current location. Save the workbook, close it, and reopen it to confirm the link holds.
- Turn on automatic calculation. If values stay stale, check the calculation setting in Excel's options and switch it to automatic. You can also press F9 to recalculate the open workbook once.
- Convert text into numbers. In the source sheet, enter 1 in an empty cell, copy it, select the text-formatted range, and multiply. Alternatively, wrap each reference in VALUE(), such as =VALUE(Sheet1!B4). Check that totals change as expected.
- Replace INDIRECT when the source is closed. INDIRECT cannot read a closed workbook and returns #REF!. Replace it with a direct reference, or open the source first.
- Retest after saving. Close and reopen both workbooks. Check one key total against the source by hand to make sure the link works from start to finish.
Prevent it next time
A few habits keep cross-sheet and cross-file references stable as files change hands.
- Use direct references where you can. A plain reference such as =Sheet1!B4 is easier to trace than INDIRECT, and it does not depend on text typed into another cell.
- Keep linked files in a stable folder. Store the source and destination workbooks together, and avoid moving one without updating its links.
- Do not rename feeder tabs casually. Before you rename a tab that other sheets read from, search the workbook for references to it.
- Document your links. A short note tab listing each linked file, its location, and its owner helps the next person repair a broken reference quickly.
- Check imported numbers. When you paste data from another system, confirm that numeric columns are real numbers before you build totals across sheets.
- Verify key totals by hand. For a 12-tab budget or a contract schedule, compare one or two cross-sheet totals with the source each time the structure changes.
❓ FAQ
1. Will Excel update linked values automatically?
It depends on whether the source workbook is open and whether you accept the update. If the source is closed, the destination keeps the last stored value until you open the source and update the links.
2. Should I copy values instead of keeping a link?
For an archived snapshot, copying values is often the simplest choice because the numbers stop changing with the source. Keep the link when the destination should follow the source, and be aware that a copied value will no longer update.
3. Why does Excel warn me about links when I open a file?
The workbook contains formulas that read from another file. Check where the source came from before you update links, especially if the file was sent by someone you do not know.
4. What if the source workbook is read-only?
A read-only source can still supply values, so the link works as long as Excel can open the file. You only need write access if you plan to edit the source itself.
📝 Summary
Broken formulas between sheets and workbooks usually trace back to a changed sheet name, a moved file, a closed source, or numbers stored as text. Read the reference, match the symptom in the table, and repair the link before you rebuild the formula. Recheck one key total after saving so you know the fix holds.
