Copy formula but skip every 4th row

sumsum213

New Member
Joined
Oct 17, 2014
Messages
12
Hi, I'm trying to copy a formula down, but need it to skip 4 rows. An example is below. I'm imagine this is an easy one, but I'm just not getting it.
=sheet1!D8
=sheet1!D9
=sheet1!D10

<tbody>
</tbody>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello,

what cell is your first formula in?

Will anything go in between the formula cells?
 
Upvote 0
Unfortunately it really isn't an easy one to do because dragging down formulas like that aren't very friendly. You can either use a one time macro:

Code:
Sub SkipFour()

    nextRow = 1


    For x = 8 To 10
        Cells(nextRow, 1).Formula = "=Sheet1!D" & x
        nextRow = nextRow + 5
    Next x


End Sub

Or you can use some helper columns that are easier to drag down:

D8 'Static=INDIRECT("Sheet1!" & A1)
D8 'Static=IF(A2<>A1,INDIRECT("Sheet1!" & A2),"DELETE ME")
D8 'Static=IF(A3<>A2,INDIRECT("Sheet1!" & A3),"DELETE ME")
D8 'Static=IF(A4<>A3,INDIRECT("Sheet1!" & A4),"DELETE ME")
D8 'Static=IF(A5<>A4,INDIRECT("Sheet1!" & A5),"DELETE ME")
=IF((RIGHT(A1)=RIGHT(A5)),"D"&RIGHT(A5)+1)... etc.
=IF((RIGHT(A2)=RIGHT(A6)),"D"&RIGHT(A6)+1)
=IF((RIGHT(A3)=RIGHT(A7)),"D"&RIGHT(A7)+1)
=IF((RIGHT(A4)=RIGHT(A8)),"D"&RIGHT(A8)+1)
... etc.

<tbody>
</tbody>
 
Last edited:
Upvote 0
Here's a way to do it without VBA or helper cells. Copy this formula into A6 and drag it down. There will still be a formula in all cells, but the value will only appear in every fourth row:

=IF(MOD(ROW(A1)-1,4)=0,INDEX(Sheet1!D:D,8+INT((ROW(A1)-1)/4)),"")
 
Upvote 0
Here's a way to do it without VBA or helper cells. Copy this formula into A6 and drag it down. There will still be a formula in all cells, but the value will only appear in every fourth row:

=IF(MOD(ROW(A1)-1,4)=0,INDEX(Sheet1!D:D,8+INT((ROW(A1)-1)/4)),"")
one thing that I like to do when I don't want a formula in the cells that are going to return "" is to just delete them.
So after you fill down Bbott's formula, hit Ctrl+G > Special Cells > formula's > Un-check Numbers > Ok > Right click on one of the highlighted cells > Clear contents.
 
Upvote 0
Hi, thank you. I tried doing it and it doesn't seem to be working.

Sheet 2 A262 needs to equal Sheet 1 C73
and then copy down by 4 rows.

Would you please write that formula out?
 
Upvote 0
bbott, never mind, I just got it. Thank you so much, you've saved me hours of work!!!

Ok, I spoke too soon. It worked for the first one, but seems to be putting it in the 4th row and not the 5th row. I tried changing the 4 to a 5, but that's not working either. OMG, I'm sorry to be annoying. Here's what the formula says as it copies down- =IF(MOD(ROW(A266)-262,4)=0,INDEX(Attendance!C:C,8+INT((ROW(A266)-1)/4)),"")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top