Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulascount-unique

ARRAYFORMULA but only untill EMPTY cells in row - doesn't work


I'm trying to copy from one tab to another with ARRAYFORMULA.

It works. but it does copy all empty cells in a row too. I don't want that.

Here is my code:

={"Elevplan - Prosa (" & TÆLV('Formularsvar 1'!C2:C) & "/" & COUNTUNIQUE('Formularsvar 1'!C2:C) & " elever)" ;
ARRAYFORMULA(
HVIS(ER.TOM('Formularsvar 1'!C2:(TÆLV('Formularsvar 1'!C2:C))); "Tom: ";

(

'Formularsvar 1'!C2:C & char(10) &
'Formularsvar 1'!D1&" "&'Formularsvar 1'!$D$2:D&" "&
'Formularsvar 1'!E1&" "&'Formularsvar 1'!$E$2:E&" "&
'Formularsvar 1'!F1&" "&'Formularsvar 1'!$F$2:F&" "&
'Formularsvar 1'!$G$2:G& char(10)

)


)
 ) 
}

It takes all cells in row C from tab 'Formularsvar 1' but the 10 last cells are empty.

How can I set a limit like C2:C22 when C is dynamic?

Sheet


Solution

  • ={"Elevplan - Prosa ("&COUNTA('Formularsvar 1'!C2:C)&"/"&
     COUNTUNIQUE('Formularsvar 1'!C2:C)&" elever)";
     ARRAYFORMULA(IF(LEN('Formularsvar 1'!C2:C),
     ('Formularsvar 1'!C2:C&CHAR(10)&
      'Formularsvar 1'!D1&" "&'Formularsvar 1'!$D$2:D&" "&
      'Formularsvar 1'!E1&" "&'Formularsvar 1'!$E$2:E&" "&
      'Formularsvar 1'!F1&" "&'Formularsvar 1'!$F$2:F&" "&
      'Formularsvar 1'!$G$2:G&CHAR(10)), "Tom: "))}
    

    0