How to autofill with non-adjacent cells

[Cornellj730]

Hi all,

I'm currently attempting to create a column with referenced cells that are separated by a row. For example, In column D: D1 would contain "=J4" , D2 would contain "=J6" , D3 would contain "=J8" , and so on. Autofill doesn't recognize this pattern when I try to use it; is there any way to get around this/get it to recognize the pattern?

[#2]

@Cornellj730

In D1:

=INDEX(J:J, 2*ROW()+2)

[#3 · Accepted solution]

@Cornellj730 Try either of following formulas-

=INDEX(J:J,SEQUENCE(10,1,4,2))
="=J"&SEQUENCE(100,1,4,2)

[#4]

@Hans VogelaarThank you so much for the help! This formula works well, but I quickly figured out that I needed something that I could manipulate a little easier (like the suggestion in the comment below). I appreciate the introduction to INDEX however. :)

[#5]

@Harun24HR I ended up using this formula because the sequence formula allows me to be a little more flexible in the values I need to call. Thank you for the help!

[#6]

Glad to know! If it helps then please mark this answer as best response to treat the question as solved.

[#7]

Hi, @Cornellj730 

Try this way in Column D:
=INDIRECT($B$1&ROW()*$B$3)
where the values in column B are variables

Regards,
Sergiusz

[#8]

@Cornellj730 

With 365 you can use array shaping formulas as an alternative to INDEX.

= CHOOSECOLS(WRAPROWS(testData, step), step)

will return the final column of each record.