Google-sheets – Combine text from multiple cells into a single cell on other Sheet

formulasgoogle sheets

I need help, I want to combine multiple cells in one other cell in another sheet.
I have 4 Collums in my first Sheet1, filed with data:

  1. Id's (A1:A)
  2. Dates(B1:B)
  3. Name(C1:C)
  4. Comments(D1:D)

And on my other Sheet2, I can select Id's(G3) from a dropdown and get more data from other sheets.
Now I want to Combine all Dates, Name and Comment in one cell on my Sheet2.
I want something like, I pick an Id on Sheet2 and get every Comment with name and Date from Sheet1 in one cell.
I want it like 

1.1.2018 peter Comment  
2.1.2018 hans Comment  
15.1.2018 peter Comment...

The cell can expand I only need everything in one cell.
Is that possible and how?

Best Answer

if you need it everything in one cell, try this:

=JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                    Sheet1!C1:C &" "&
                                    Sheet1!D1:D); Sheet1!A1:A=G3))

blank error fix:

=IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                               Sheet1!C1:C &" "&
                                               Sheet1!D1:D); Sheet1!A1:A=G3));)

Related Topic