Dragging down Arrayformla

I have a Arrayformuöla and importrange to import the data in the cell from an spreadsheet to another spreadsheet. I have figured out how and it works. But i have to write or copy paste the formula to each cell down manually and making after the Changes.How can I drag down the formula to fill out autonatically? I have on spreadsheet 1 in cell C2 K2 and L2 data and I want to transfer the 3 Ciollumns to another spreadsheet in the same cell. As i said it is working but if i drag down the formula instaed of c3 k3 l3 to fill out it still the same.

this is my formula

=arrayformula(importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgcQ/edit", "C2") & " " & importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgc/edit", "K2")& " " &importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgc/edit", "L2"))

This goes into second row of the sheet you are using this formula; like lets say you paste this in Cell A2 then C2,K2,L2 of the source sheet are pulled in & as you drag it down subsequent rows of data are auto-pulled.

If your sheet locale uses ; convention instead of comma then you may want to change all the , in the formula.

Try:

=lambda(z,join(" ",index(z,row(),1),index(z,row(),9),index(z,row(),10)))(importrange("14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgcQ", "C1:L100"))

There is more than one way to do that. Basically you're not working with cells specifically but to text that refers to the location of a cell. That's why it doesn't drag. One with be with string management and associating it to the number of row. For example, if you are positioned in the second for row:

=arrayformula(importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgcQ/edit", "C"& ROW()) & " " & importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgc/edit", "K" & ROW())& " " &importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgc/edit", "L" & ROW() ))

You could change ROW() with ROW()+1 if you were starting in row 3 for example.

But, what I'd suggest is to do only once the IMPORTRANGE and use BYROW to do the joining. Let's say you need it until row 100:

=BYROW({importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgcQ/edit", "C2:C100"),importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgc/edit", "K2:K100"),importrange("https://docs.google.com/spreadsheets/d/14cyHIzdBHSFa4hgj0S0bCIFk7CC-zSw4g3ngI7UNgc/edit", "L2:L100")}, LAMBDA(imported, TEXTJOIN(" ",1,imported)))

It would be much more efficient. Let me know if it's useful