Forum Discussion

AnyStap's avatar
AnyStap
Copper Contributor
Jun 03, 2020

TEXTJOIN

I am trying to create a Risk Map using the TEXTJOIN function. I would be extracting data from a Risks tab to automatically updating the Risk Map. The formula I found was ="•"&TEXTJOIN(CHAR(10)&"•",TR...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 03, 2020

AnyStap 

The TEXTJOIN itself looks OK but I do not recognise the references risks!! and risks![Title] to the text you are trying to concatenate.  Are they meant to be references to columns within a table, defined names or range references?

 

 

  • AnyStap's avatar
    AnyStap
    Copper Contributor
    Jun 03, 2020

    PeterBartholomew1 Yes. They are supposed to be references to columns within a table. I tried removing the "!" and tried "•"&TEXTJOIN(CHAR(10)&"•",TRUE,IF(risks[likelihood]=$C3,IF(risks[impact]=D$8,risks![Title],""),"")). to no avail. 

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jun 03, 2020

      AnyStap 

      looks like you are getting close.

      = "•"&TEXTJOIN(CHAR(10)&"•",TRUE,

      IF((risks[Likelihood]=$E15)*(risks[Impact]=F$14), risks[Title],"") )

      Because I use Office 365 my formula is slightly different

      = "• " & TEXTJOIN(CHAR(10) & "• ", TRUE,

      IF((risks[Likelihood]=@LikelihoodLevel)*(risks[Impact]=@ImpactLevel), risks[Title],"") )

      To propagate the formula across columns select the range and use Ctrl+Enter rather than fill right.

    • Dustin-Doucette's avatar
      Dustin-Doucette
      Brass Contributor
      Jun 03, 2020
      Can you post what the error returns as? I see nothing wrong with your above formula technically, although I would say I recommend making your RISKS table reference columns static. Like this:
      =TEXTJOIN(CHAR(10)&"•",TRUE,IF(RISKS[[LIKELIHOOD]:[LIKELIHOOD]]=$C3,IF(RISKS[[IMPACT]:[IMPACT]]=D$8,RISKS![[TITLE]:[TITLE]],""),""))

      I have a near identical formula but instead of referencing risks I'm referencing start and end dates, and I'm pulling multiple columns into one text string for each matched date. Here's my exact formula:
      =TEXTJOIN(CHAR(10),TRUE,IF(MyTable[[Start Date]:[Start Date]]<=C14,IF(MyTable[[End Date]:[End Date]]>=C14,MyTable[[Name]:[Name]]&" "&MyTable[[Job]:[Job)]],""),""))