Error in Textjoin formula

Question

Textjoin sometimes gives empty results: 

============================== 

Textjoin formula is found in "Annual_leaves" Sheet 

=TEXTJOIN("+",TRUE,IF((Report!$B$10:$B$200=$A2)*(Report!$C$10:$AD$200="A"),Report!$C$8:$AD$8,""),"") 

examples of the results: 

"16" or"16+21+23" or "02+11+24+27" 

My formula usage: 

============== 

It brings annual leaves dates from sheet "Report" for every employee 

The problem: 

========== 

>>>When you edit a cell in another sheet rather than "Report",textjoin will show blank results 

Steps to produce the problem: 

====================== 

1-Edit any cell in sheet "Attendance" 

2-then go to sheet "Annual_leaves" ,you will find textjoin show empty results

Attachment:

==========

I created a post in Microsoft uservoice site:It contains an attachment

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/33822259-error-in-textjoin-formula

Answer 1

Hi Yasser,

I could understand your feeling.

However, I would like to explain that I suggest you try creating a simple formula to check if the issue occurs.

As on the affected file, the formula is complicated, we need to use simple formula to narrow down if the issue is caused by building formula.

When we create the textjoin formula, we didn't meet the issue.

Thanks,

Qing

Answer 2

Textjoin is a new formula and new formulas are experimental and they may have some issues. I know textjoin may work in different cases, But in my case textjoin doesn't work, so I can say it`s a bug.

So please help me and fix this bug

Answer 3

Hi Yasser,

Thanks for your reply.

I would like to explain that I didn’t mean Textjoin formula conflict with other formula.

I mean may be when we change the data of Attendance, it will change the date on Report.

If we couldn’t meet the formula “IF((Report!$B$10:$B$200=$A2)*(Report!$C$10:$AD$200="A")”, it will display error or blank.

Based on my test, as I create a simple file to check, there is no issue.

Thanks,

Qing

Answer 4

Hi Yasser,

I suggest you check if the formula which is used to get data from Attendance to Report has some conflict after you change data on Attendance sheet.

Besides, you can check a simple data to check if the issue occurs on your computer to narrow down the issue.

Thanks,

Qing

Textjoin should don`t conflict with any other formula because they are all made by Microsoft.

So please fix your formula"Textjoin"

Answer 5

Hi Yasser,

Based on your description, I would like to explain that Textjoin formula on "Annual_leaves" Sheet 

=TEXTJOIN("+",TRUE,IF((Report!$B$10:$B$200=$A2)*(Report!$C$10:$AD$200="A"),Report!$C$8:$AD$8,""),"") has no relation with the Attendance sheet, it only get the data from the report sheet.

So, the issue may be not related to textjoin formula, I suggest you check if the formula which is used to get data from Attendance to Report has some conflict after you change data on Attendance sheet.

Besides, you can check a simple data to check if the issue occurs on your computer to narrow down the issue.

Thanks,

Qing