CODEWITHSUNDEEP
google-sheetsreferenceincrementgoogle-sheets-formulaarray-formulas
TraaZ
TraaZ

Reputation: 81

Increment non-adjacent cell's reference by 1 in Google Sheets

I have a sheet with this:

Sheet6

I'm trying to collect these names in a different Sheet so it can load an API but that API loads 10 lines so between each name I need a 10 line space, leaving it like this:

Api

Right now if I copy and paste A1 (Which formula is: Sheet6!A2) in A21 it will appear as Sheet6!A22 instead of Sheet6!A4.

I've done this manually but I have hundreds of these so I'd like a way to get these cell references just by dragging, copy-paste or any other faster way.

Upvotes: 1

Views: 130

Answers (1)

player0
player0

Reputation: 1

you could do:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY({
 "♦"&INDIRECT("A2:A"&COUNTA(A1:A))&"♠Sheet6!A"&
 ROW(INDIRECT("A2:A"&COUNTA(A1:A)))&"♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦"},,999^99), "♦")), "♠"))

0

if you just want to add 9 empty rows between names do:

=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY({
 "♦"&INDIRECT("A2:A"&COUNTA(A1:A))&"♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦ ♦"},,999^99), "♦")))

0

or like this:

=ARRAYFORMULA(TRANSPOSE(SUBSTITUTE(SPLIT("♦"&TEXTJOIN(REPT("♠♦", 10), 1, A2:A), "♦"), "♠", )))

0

Upvotes: 1

Related Questions