AutoFill with sheet reference help

  • Thread starter Thread starter TimeCloud
  • Start date Start date
T

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
 
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.
 
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...
 
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.
 
Worked great.. Thank you very much,

T. Valko said:
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.
Click to expand...
 
Back
Top