How to use concatenate function inside arrayformula function in google sheets

user_16612255 0
Viewed 47

I'm currently using the following formula to concatenate a few cells into a single description sort of entry in the same row, as shown here

CONCATENATE("Conducting Person : ",H2,CHAR(10),"Tech Support : ",J2,CHAR(10),"Category : ",F2,CHAR(10),"Room : ",G2,CHAR(10),"Platform : ",K2), "")

This formula is working fine but when I add a new entry in the blank row right below that last filled row, I have to manually add the formula to the required cell.

I tried the following arrayformula :

=ARRAYFORMULA(IF(NOT(ISBLANK(A2:A)),CONCATENATE("Conducting Person : ",H2,CHAR(10),"Tech Support : ",J2,CHAR(10),"Category : ",F2,CHAR(10),"Room : ",G2,CHAR(10),"Platform : ",K2), ""))

That seems to be executing the formula when an entry is made in the first cell of the blank row below the last filled row, but the cell positions are not relatively changing and seem to be fixed to the initial cell reference.

How do I fix it?

google-sheets-formula google-sheets
2 Answers

Try

=arrayformula("Conducting Person : "&H2:H&CHAR(10)&"Tech Support : "&J2:J&CHAR(10)&"Category : "&F2:F&CHAR(10)&"Room : "&G2:G&CHAR(10)&"Platform : "&K2:K)
user_150193800

use:

=ARRAYFORMULA(IF(ISBLANK(A2:A),,
 "Conducting Person : "&H2:H&CHAR(10)&
 "Tech Support : "&     J2:J&CHAR(10)&
 "Category : "&         F2:F&CHAR(10)&
 "Room : "&             G2:G&CHAR(10)&
 "Platform : "&         K2:K)))
user_56326290
Related