1

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

Allan Bech
  • 391
  • 1
  • 6

1 Answers1

0
={"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

player0
  • 124,011
  • 12
  • 67
  • 124
  • It is working - almost. It doesn't say "Tom or Empty" and somehow the output-tab; Til kopering produces an extra 500 rows. – Allan Bech Jul 07 '19 at 15:35
  • 1
    try: `={"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: "))}` – player0 Jul 07 '19 at 15:46
  • Works perfect! Still another 500 rows though. – Allan Bech Jul 07 '19 at 15:47
  • Things kinda grew... I can add a new question if I'm supposed to? The question is; Is it possible to ad an Select... Where Column Klasse i i.e. 7L and group it on another tab? – Allan Bech Jul 07 '19 at 23:23
  • like this? `={"Elevplan - Prosa ("&COUNTA('Formularsvar 1'!D2:D)&"/"& COUNTUNIQUE('Formularsvar 1'!D2:D)&" elever)"; ARRAYFORMULA(IF((LEN('Formularsvar 1'!D2:D))*('Formularsvar 1'!C2:C="7L"), ('Formularsvar 1'!D2:D&CHAR(10)& 'Formularsvar 1'!E1&" "&'Formularsvar 1'!$E$2:E&" "& 'Formularsvar 1'!F1&" "&'Formularsvar 1'!$F$2:F&" "& 'Formularsvar 1'!G1&" "&'Formularsvar 1'!$G$2:G&" "& 'Formularsvar 1'!$H$2:H&CHAR(10)), ))}` – player0 Jul 07 '19 at 23:42
  • Yes. And no. Jobs done but result is spread all over sheet. Is it possible to show them ordered? – Allan Bech Jul 07 '19 at 23:50
  • like this? `={"Elevplan - Prosa ("&COUNTA('Formularsvar 1'!D2:D)&"/"& COUNTUNIQUE('Formularsvar 1'!D2:D)&" elever)"; QUERY(ARRAYFORMULA(IF((LEN('Formularsvar 1'!D2:D))*('Formularsvar 1'!C2:C="7L"), ('Formularsvar 1'!D2:D&CHAR(10)& 'Formularsvar 1'!E1&" "&'Formularsvar 1'!$E$2:E&" "& 'Formularsvar 1'!F1&" "&'Formularsvar 1'!$F$2:F&" "& 'Formularsvar 1'!G1&" "&'Formularsvar 1'!$G$2:G&" "& 'Formularsvar 1'!$H$2:H&CHAR(10)), )),"where Col1 is not null", 0)}` – player0 Jul 07 '19 at 23:53
  • Can I get you some coffee? The empty rows are still showing - any way to get rid of them? – Allan Bech Jul 07 '19 at 23:58