Copy / drag formula to next nth row

Hi guys,

Please help, is there a formula wherein, if dragging it down 5 rows lower the cell reference should still be in series. example e3=+sheet1!$A1, dragging it down to e8 the formula should be e8=+sheet1!$A2, but as i do it the formula i get is e8=+sheet!$A6.

please see attached file for clearer example.

Thank you so much =)

try
=IF(MOD(ROWS($A$1:A1)-1,5),E2,INDEX(Sheet1!$A:$A,CEILING(ROWS($A$1:A1)/5,1)))

=INDIRECT("sheet1!$A"&ROUNDUP((ROW()-2)/5,0))

or try this one

popipio that is exactly the same as =INDEX(Sheet1!A:A,CEILING(ROWS($A$1:A1)/5,1)) but that's not what the op asked for !
but i agree it gives the same result ie repeating the value 5 times. which is probably what the op is trying to achieve.

Hi

plz see the formula in Col F

Hi guys!!!

Your so brilliant!!!!
@martindwilson - your formula really works!!!!
@rajeshturaha - thanks man for the file.
@popipipo - your formula works too....

I LOVE THIS FORUM!!!! THANKS GUYS!!!! =)