Forum Discussion
identical TEXTJOIN formula works in one workbook but not another
Hello Excel gurus.
I have a spreadsheet in which I am listing multiple matching values based on a another match, and combining those matching values using TEXTJOIN.
The spreadsheet was set up last year and it worked perfectly. After a save-as, and some adjustmetns to the data, it now produces a #REF error, despite it being exactly the same formula that worked last year. This is now the second formula in the same file which no longer works despite being eaxctly the same as something created a year ago.
When I create a new, dummy file, and enter an identical formula, it also works in the new file.
As an example, in the dummy file, there is Sheet1 and Sheet3. Sheet 3 looks like this (I've simplified it for demonstration purposes):
Column A is a 'helper' column which concatenates B and D.
Sheet1 looks like this, and uses the formula
=TEXTJOIN(", ",TRUE,IF($A6&B$4=Sheet3!$A:$A,Sheet3!$C:$C,""))
in cells B5-B12. The same formula is in columns C and D, except the B$4 in the formula adjusts to C$4 and D$4. Here is what the results look like in the dummy file, which functions properly.
The only thing that is different about the formula that works and the one that doesn't is that the name of Sheet3 is different in the file which does not function.
Any idea what would cause formulas to just stop working in a file, despite the identical formula working perfectly well in another file?
Thanks
6 Replies
- Just try with giving specific data range
=TEXTJOIN(", ",TRUE,IF($A6&B$4=Sheet3!$A1:$A200,Sheet3!$C1:$C200,""))- I tried that. It doesn't work.
- If you look at where the #REF occurs in the formula, it should give you a better idea of what is broken (#REF usually occurs when something the formula is referring to, like a range or worksheet is completely deleted).
In the workbook that does not function, was the worksheet the formula refers to deleted and replaced with a new version/copy?- No, the worksheet has existed in the same form throughout. It is a separate worksheet within the same workbook. The range still exists. I have tried clearing the cell and retyping the formula manually, but it doesn't change the result.
Could you attach a copy of the problem workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- It will be quite a mission to strip the problem workbook of sensitive data. I will try, but it might break some other links. Let me see what I can do.
