Fix a TEXTJOIN Formula Returning #REF! in an Excel Workbook
Question details
The user needs to find why the same TEXTJOIN and IF formula works in one Excel workbook but returns #REF! in another.

- 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.
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.
Repair broken worksheet and range references
Use this method when a worksheet, range, or other referenced object may have been deleted or replaced.
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.
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.
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.
Press Enter after editing the formula and check whether the result appears. If #REF! remains, inspect the source cells and remaining references for errors.

Replace full-column array references with matching ranges
Use finite ranges to limit the array calculation and make the referenced cells easier to inspect.
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.

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.




