AutoFill with sheet reference help

[TimeCloud]

I am trying to use autofill to copy a sheet reference as follows:

=sheet1!a1
=sheet1!a1
=sheet1!a1
=sheet1!a2
=sheet1!a2
=sheet1!a2
=sheet1!a3
=sheet1!a3
=sheet1!a3

The autofill can't figure out my pattern and will not fill in the formula
properly it skips numbers.

TIA

 

[T. Valko]

Assume you enter the first formula on some sheet in cell A1:

=INDEX(Sheet1!A:A,CEILING(ROWS(A$1:A1)/3,1))

Copy down as needed.

 

[TimeCloud]

Tell me where to send the beer... Thanks a ton!!!

 

[TimeCloud]

OOPS on my part...

It works for Column A though which is the first part. I forgot about Column
B :-/

A B
sheet1!a1 sheet1!c1
sheet1!a1 sheet1!d1
sheet1!a1 sheet1!e1
sheet1!a2 sheet1!c2
sheet1!a2 sheet1!d2
sheet1!a2 sheet1!e2

Sorry about that.. and Thanks...

 

[T. Valko]

For column B, entered in B1:

=INDEX(Sheet1!C:E,INT(ROWS(B$1:B3)/3),MOD(ROWS(B$1:B3),3)+1)

Copy down as needed.

 

[TimeCloud]

Worked great.. Thank you very much,

For column B, entered in B1:

=INDEX(Sheet1!C:E,INT(ROWS(B$1:B3)/3),MOD(ROWS(B$1:B3),3)+1)

Copy down as needed.

 

[T. Valko]

You're welcome. Thanks for the feedback!