2

I'm trying to use auto-fill to continue a pattern, however it's skipping several numbers as it fills.

For example, this is what I'm trying to accomplish:

Column A

=Sheet1!A1

=Sheet1!B1

=Sheet1!C1

Using auto-fill, however, skips 3 each time, so it ends up like this:

=Sheet1!A4

=Sheet1!B4

=Sheet1!C4

=Sheet1!A7

=Sheet1!B7

=Sheet1!C7

What I'd like the next part of the pattern to fill to is (thanks @ScottCraner for clarifying):

=Sheet1!A2

=Sheet1!B2

=Sheet1!C2

Does anyone have any ideas on how to fix this, or an alternate method of filling the pattern? Thanks!

  • 1
    So the next in your desired sequesnce is =Sheet1!D1 or is it =Sheet1!A2? – Scott Craner Aug 30 '16 at 19:53
  • 1
    @ScottCraner The latter. Thank you for pointing out the clarification, I edited that into the question. – Salmononius2 Aug 30 '16 at 20:01
3

This formula will give the desired pattern as you fill down:

=INDEX(Sheet1!A:C,INT((ROW(1:1)-1)/3)+1,MOD(ROW(1:1)-1,3)+1)

If you are filling accross replace both ROW(1:1) with COLUMN(A:A)

To show it works I filled Sheet 1 columns A:C with their addresses:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.