Formula Errors

Fix a TEXTJOIN Formula Returning #REF! in an Excel Workbook

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 869 views

Question details

The user needs to find why the same TEXTJOIN and IF formula works in one Excel workbook but returns #REF! in another.

Fix a TEXTJOIN Formula Returning #REF! in an Excel Workbook
Product
Microsoft Excel
Device & OS
not provided
Scenario
After saving a workbook under a new name and changing its data, the user applies a formula that references a worksheet and entire columns.
Observed behavior
The formula works in a test workbook but returns #REF! in the other workbook.
Before you start

Open both workbooks and identify the cell containing the formula in each. Check the name and location of the source worksheet in the workbook that returns the error.

Solution 1Recommended

Repair broken worksheet and range references

Use this method when a worksheet, range, or other referenced object may have been deleted or replaced.

1
Inspect the formula

Select the failing cell and examine its formula in the formula bar. Look for #REF! within the formula, which can indicate the location of a broken reference.

2
Check the source sheet

Compare the worksheet tabs with the sheet names in the working formula. If a referenced sheet was deleted and replaced, edit the formula to point to the appropriate sheet and range.

3
Check copied references

Compare the failing formula with the one in the test workbook. If it contains a workbook or worksheet reference that does not match the intended source, edit that reference in the formula bar.

4
Test the corrected formula

Press Enter after editing the formula and check whether the result appears. If #REF! remains, inspect the source cells and remaining references for errors.

Repair broken worksheet and range references
A replacement sheet may not repair a reference: Deleting a referenced sheet can turn its reference into #REF!. Adding another sheet, even with the same name, may require you to edit the formula again.
Free Microsoft Office alternative

Review spreadsheet formulas with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for moving existing files. You can review and edit formulas in WPS Spreadsheets, but switching apps does not automatically repair broken Excel references.

Inspect and edit worksheet references in a familiar spreadsheet interfaceOpen and save Microsoft Excel XLSX files for easier migrationUse a free, lightweight office suite for everyday spreadsheet work
microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula show #REF! after a sheet was replaced?

Deleting a referenced sheet can break the formula's reference. Select the formula cell, inspect the formula bar, and replace the broken reference with the correct sheet and range.

Will changing full-column references fix every #REF! error?

No. Finite ranges can make an array formula easier to manage, but you must also repair any deleted or invalid references. Check the formula bar and the referenced source cells.

Why must the IF ranges cover the same rows?

The comparison range and return range should align so each match corresponds to the intended return value. For example, pair Sheet3!$A$1:$A$200 with Sheet3!$C$1:$C$200.

Do I need Ctrl+Shift+Enter for this TEXTJOIN formula?

In Excel versions that support TEXTJOIN but do not support dynamic arrays, the IF array formula may need Ctrl+Shift+Enter. In dynamic-array versions, press Enter normally.