Forum Discussion

CFYeap's avatar
CFYeap
Copper Contributor
Aug 02, 2022
Solved

ARRAYTOTEXT with CELL function doesn't work?

Hi, I am trying to generate a list (text) from my data and I couldn't get the ARRAYTOTEXT to work. I am using Microsoft 365 version 2206 64 bit (15330.20264 click-to-run), running on Windows 10. Please find sample file here.

 

My aim is to generate a list (in this sample, a list of activities done) by selecting the range of travel dates. For example, if the travel date range from 2018-2020, the generated list should contain all the activities done within the duration. The only input should be "selecting the date range".

 

Since I am working with text, I used ARRAYTOTEXT to join the contents of the arrays. This is possible when the cell range is typed into the ARRAYTOTEXT formula manually, but it ends up with "there's a problem with the formula" when I try to refer the cell range from another cell (ie. D2, D3 in the excel sheet 'generate list'). I used CELL function combined with ARRAYTOTEXT.

 

The expected result should be as in D6. The problem I faced happened in C6. Does anyone have an idea what other function can I use other than these 2 to generate the list, or what can I do to make them work?

 

Thank you!

 

 

excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Aug 03, 2022

    Hi CFYeap 

     

    Alternatively, in 'generate list'!D6:

    =LET(
    From, XMATCH(C2,Table1[Date]),
    To,   XMATCH(C3,Table1[Date]),
    TEXTJOIN(CHAR(10),,INDEX(Table2[Event], SEQUENCE(To-From+1,,From)))
)

7 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 03, 2022

    Hi CFYeap 

     

    Alternatively, in 'generate list'!D6:

    =LET(
    From, XMATCH(C2,Table1[Date]),
    To,   XMATCH(C3,Table1[Date]),
    TEXTJOIN(CHAR(10),,INDEX(Table2[Event], SEQUENCE(To-From+1,,From)))
)
    • CFYeap's avatar
      CFYeap
      Copper Contributor
      Aug 03, 2022

      Hi Riny_van_Eekelen,

       

      Thanks for the suggestion. That will give a whole list of all events ever done (result from your formula in E6, versus the desired result in D6). I would like to display only those events happened within the range specified from date in C2 to C3.

       

       

Resources