Sep 22 2022 08:42 AM
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
Sep 22 2022 12:39 PM
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?
Sep 22 2022 12:50 PM
Sep 22 2022 01:00 PM
Sep 22 2022 01:02 PM
Sep 22 2022 11:10 PM
Sep 23 2022 12:03 AM