# Office 365 >  >  Textjoin sometimes works well and sometimes doesnot

## leprince2007

*Textjoin sometimes works well and sometimes doesnot:*
Textjoin formula is found in "Annual_leaves" Sheet



```
Please Login or Register  to view this content.
```


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

----------


## AliGW

Problem confirmed - I don't know why this is happening. Hopefully someone else with Office 365 can help.

----------


## teylyn

Hello,
I can't reproduce the behaviour. I'm on build 1804 (9219.2009), so maybe the bug has been fixed.

----------


## AliGW

I’m pretty sure mine is completely up-to-date but will check in the morning.

----------


## leprince2007

> Hello,
> I can't reproduce the behaviour. I'm on build 1804 (9219.2009), so maybe the bug has been fixed.



*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

*My Office 365 version is:
*


```
Please Login or Register  to view this content.
```


*I checked for updates but it told me:
*


```
Please Login or Register  to view this content.
```

----------


## AliGW

I'm on 1802, but the problem is there. I am just downloading updates now and will report back if the issue goes away afterwards.

----------


## AliGW

I'm now on version 1803 and it doesn't work at all in the sample file attached to post #1. I am guessing that 1804 is the insider version: I'm not on the insider channel.

----------


## xlnitwit

I can replicate the problem using the same build as teylyn- 1804 (9219.2009)

----------


## leprince2007

> I'm now on version 1803 and it doesn't work at all in the sample file attached to post #1. I am guessing that 1804 is the insider version: I'm not on the insider channel.







> I can replicate the problem using the same build as teylyn- 1804 (9219.2009)



How can we fix this bug?

----------


## AliGW

We can't fix it if it is a bug, but you can report the behaviour to Microsoft.

----------


## AliGW

You could try this (array entered):

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

----------


## leprince2007

> You could try this (array entered):
> 
> =TEXTJOIN("+",TRUE,IF((Report!$B$10:$B$200=$A2)*(Report!$C$10:$AD$200="A"),Report!$C$8:$AD$8,""))



I can reproduce the same problem

----------


## AliGW

It works on my version:

*Excel 2016 (Windows) 32 bit*

C

*1*
*Actually*

*2*


*3*
*01*

*4*


*5*
*06*

*6*
*01+26*

*7*


*8*
*19+25*

*9*
*05*


Sheet: *Annual_leaves*



*Excel 2016 (Windows) 32 bit*

C

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


Sheet: *Annual_leaves*



Version 1803 9126.2116

----------


## AliGW

Ugh!!! No, it fails if I edit a value in the attendance sheet.  :Mad:

----------


## AliGW

I think you need to troubleshoot the formula used on the Report tab - it references the All Leaves tab which is populated manually, so the minute you change anything anywhere, at least one of those manual entries could/will be wrong, as it's not recalculating.

----------


## leprince2007

> Ugh!!! No, it fails if I edit a value in the attendance sheet.



What can we do now to fix this bug?
How can I report this bug to Microsoft?

----------


## AliGW

I have explained in post #15 what I think you need to do. Remember, you have created these complex formulae, so you know what they are meant to be doing, so you need to troubleshoot them.

To report bugs to Microsoft, you need to log into your 365 account. However, for the reasons stated, I don't think this is a bug - I think it's something fundamental to the way you have set up your workbook.

I am sorry, but I don't have the time to spend going through every formula in your workbook to work out what they are doing and to try to identify at which point they are failing: you need to do that. Use the Evaluate Formula feature to help you. Once you have identified where it is going wrong (I don't believe it's the TEXTJOIN formula), try to fix it. If you can't fix it, come back here for help.

Good luck!  :Smilie:

----------


## leprince2007

> I think you need to troubleshoot the formula used on the Report tab - it references the All Leaves tab which is populated manually, so the minute you change anything anywhere, at least one of those manual entries could/will be wrong, as it's not recalculating.



Everything works fine in my workbook except textjoin formula.So If you see anything wrong, please suggest the correct change for it.

----------


## AliGW

See post #17. I can't see anything wrong _per se_ with the TEXTJOIN formula.

----------


## leprince2007

> See post #17.



I tried different scenarios:
1-I converted the formulas in Report to values,the problem disappeared completely
2-I changed the formula-which you say it contains a problem-to a normal index formula,I can reproduce the problem again.
See the attchment:"Report" contains values only."Report2" contains index formula
I can say that the problem happens only when:
When textjoin depends on a range which contains formulas(any formula)

----------


## AliGW

Then you need to report this as a bug to Microsoft, as you seem to have tested it thoroughly.

----------


## leprince2007

> Then you need to report this as a bug to Microsoft, as you seem to have tested it thoroughly.



How to report this bug?
I think multiple people should report it also in order to correct it

----------


## AliGW

See post #17.  :Smilie: 

I am sorry, but I am not going to report it for you.

http://www.schveiguy.com/blog/2017/0...-to-microsoft/

----------


## leprince2007

> See post #17. 
> 
> I am sorry, but I am not going to report it for you.
> 
> http://www.schveiguy.com/blog/2017/0...-to-microsoft/







> I can replicate the problem using the same build as teylyn- 1804 (9219.2009)



I created A post in Microsoft uservoice site
Please support me to fix the problem
https://excel.uservoice.com/forums/3...xtjoin-formula

----------


## AliGW

I have voted for this. It could be some time before anything is done, if at all, so don't expect a quick fix.  :Smilie:

----------


## leprince2007

> I have voted for this. It could be some time before anything is done, if at all, so don't expect a quick fix.



Thank you for your vote!!
How Can I invite all forum members to vote to fix this bug?
Can I post in other forums?

----------


## AliGW

You must not do that. I have been helping you with this, so I have voted, but you should not be asking others to help out unless they have been involved in this thread. It is up to the individuals concerned to see this and choose whether or not to vote. Please do not post a thread asking for help or PM members to do so: that would be an abuse of the PM system. You should not spam other forums with the request, either: that would be very bad practice.

You just have to hope that others will vote.

----------


## xlnitwit

Uservoice is not for bug reports anyway, so I think your vote would be wasted. I would suspect that they will ignore or delete that suggestion.

----------


## AliGW

I wouldn't know. Never had to do so. Do you have a link?

----------


## xlnitwit

A link for what?

----------


## AliGW

I thought you might know where the OP should report bugs.

----------


## xlnitwit

For 2016 you can use the feedback icon in the top right. Or you can waste a couple of hours ringing them. I pass them on to colleagues in IT to pass through our support contract but I can’t say as I’ve ever seen one get fixed- though none have been exactly showstoppers, to be fair.

----------


## leprince2007

I could make  textjoin work with index formula in post #20 but only after converting it to array formula



```
Please Login or Register  to view this content.
```


But my original formula in post 1 is already an array.Textjoin doesn`t work with it:



```
Please Login or Register  to view this content.
```


Can anyone help me with this problem?

----------


## xlnitwit

If the formula in post 20 works, why not use it?

----------


## leprince2007

Because it`s a different formula from the formula that I use

----------


## xlnitwit

But you said you could make it work, whereas your formula does not work.

----------


## leprince2007

Thank you very much
I deleted the formula until Microsoft fixes it

----------

