0

I'm trying to import some specific cells from one worksheet to another, and I want to do so in a repeating, intermittent way.

For instance, if A51 is the first cell to be imported, I want to import A51 and all "reoccuring next fifth cells" in the same row (A56, then A61, then A66 and so on).

I figured I'd only need to use ArrayFormula, ImportRange and Offset, but I couldn't be more wrong. In my inexperienced mind, the formula would look something like this:

=ARRAYFORMULA(OFFSET(IMPORTRANGE("url";"Page1!A51:51");0;5))

... but it obviously doesn't work. I keep falling into the same problem no matter how much I tweek it, "the argument must be an interval". I've stumbled across similar problems here in StackOverflow, but none of them were quite the same issue; I tried their solutions and they also didn't work.

e.g. Exemple

So now I resort to you, great SO community. Could anyone explain to me why am I failing so miserably in a (most probably) simple task? I appreciate any help!

Thanks!

Felipe Carvalho Cortez
  • 1
  • 1
  • Can you share a sample of what the range you are importing looks like? – Aerials Apr 23 '21 at 11:50
  • Of course! It would be A51:51 I believe, unless I'm misunderstanding something. D: – Felipe Carvalho Cortez Apr 23 '21 at 17:15
  • What I mean is can you share a sample sheet illustrating the "from: -> to:" that you want to achieve with the formulas – Aerials Apr 26 '21 at 14:02
  • @Aerials Sorry for taking this long to reply! I made two sample sheets, one containg the desirable input information and the other one where it all should end up, after importing that info. Here are the links: Import WorkSheet: [link](https://docs.google.com/spreadsheets/d/1el8PStX7NxRLbL-LC1afPTY3zAv2QqaQotRR18o0EjM/edit?usp=sharing). Output WorkSheet: [link](https://docs.google.com/spreadsheets/d/1zVLB2g4MYZjZdephupg_BhdVPCclyzyjPwyQo8ONcq4/edit?usp=sharing). – Felipe Carvalho Cortez May 01 '21 at 22:39
  • Your example sheets are not ordered like your questions. You talk about cell A51, and there is nothing there. Then you talk about offset 5 cells, it looks like your example you use 2 cells offset. You use colors but not explain them. I'm sorry if I miss understood, but your question is still not clear. – Aerials May 13 '21 at 08:20

1 Answers1

0

First see if you can just omit blank values and get the result you want. In the Input spreadsheet, choose Insert > New sheet and put this formula in cell E4 of the new sheet:

=query( transpose('Página1'!B4:4); "where Col1 is not null"; 0 )

You can then do the same in the Output spreadsheet with these formulas:

=query( transpose(importrange("1el8PStX7NxRLbL-LC1afPTY3zAv2QqaQotRR18o0EjM"; "Página1!B4:4")); "where Col1 is not null"; 0 )

=query( transpose(importrange("1el8PStX7NxRLbL-LC1afPTY3zAv2QqaQotRR18o0EjM"; "Página1!B6:6")); "where Col1 is not null"; 0 )

If that is not what you needed, please show more realistic-looking sample data in the Input spreadsheet and the hand-entered results you would like to get from that data in the Output sheet. Do not show ColB;Row4 but the actual results you would like to get.

doubleunary
  • 13,842
  • 3
  • 18
  • 51