Textjoin sometimes works well and sometimes doesnot

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

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

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

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

Originally Posted by teylyn

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.

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.

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)

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

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,""))

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

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

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.

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!

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

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

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 have voted for this. It could be some time before anything is done, if at all, so don't expect a quick fix.

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.

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.

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

A link for what?

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

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.

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

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