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

49 Views Asked by At

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

1

There are 1 best solutions below

8
player0 On BEST ANSWER
={"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