fill down formula incrementing by every 12 rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I want to drag/fill down a formula in 12 rows (eg A1 to A12)as follows
=Sheet1A2
=Sheet1A2
=Sheet1A2
=Sheet1A2
=Sheet1A3
=Sheet1A3
=Sheet1A3
=Sheet1A3
I want it to increment by 1. eg next 12 rows would =Sheet1A4 etc
Selecting the entire range does not work, it just keeps returning the same
value eg Sheet1A2
Thanks
Craig
 
So, you want to repeat the reference 4 times then increment by 1?

Try this:

=INDEX(Sheet1!A$2:A$65536,CEILING(ROWS($1:1)/4,1))
 
Another way

In say, Sheet2's B2, copied down as far as required:
=OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/4),)
will "repeat the reference 4 times then increment by 1"

The starting reference is:
=Sheet1A2
 
Back
Top