# There has to an easier way?????????

#### JoshuaMars

##### New Member
I have over 350 more rows of this to formula to do, but im sure there is an easier formula that doesnt take so long.

C5 down to C14 is the same formula =INDEX(DAILY!C:C,ROW()+0)+INDEX(DAILY!C:C,ROW()+11)+INDEX(DAILY!C:C,ROW()+22)+INDEX(DAILY!C:C,ROW()+33)+INDEX(DAILY!C:C,ROW()+44)+INDEX(DAILY!C:C,ROW()+55)+INDEX(DAILY!C:C,ROW()+66)

the above formula goes up in 11

then the next row does a sum of C5 to C14
=SUM(C5:C14)

C16 down to C25 is the same formula =INDEX(DAILY!C:C,ROW()+66)+INDEX(DAILY!C:C,ROW()+77)+INDEX(DAILY!C:C,ROW()+88)+INDEX(DAILY!C:C,ROW()+99)+INDEX(DAILY!C:C,ROW()+110)+INDEX(DAILY!C:C,ROW()+121)+INDEX(DAILY!C:C,ROW()+132)

agian the above formula goes up in 11

then the next row does a sum of C16 to C25
=SUM(C16:C25)

this continues down the page until row 576.

Hope someone can think of something.

Joshua

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hello Joshua,

I first tried to handle this formula but I gave up, I think I am better with macro.

So I wrote a code that will use the information you have provided and will just automatise the copy adding as well the sum as you mentionned.

My understanding is the formula will be copied down from row5 column3 in one worksheet, the formula reference another sheet called daily.

You will have from r5c3 10 cell with the formula then a cell with the sum of the 10 previous row.
After each sum the value in your formula will increment by eleven

To try out my code use a copy of your workbook. Place my code on the worksheet where the formula are supposed to go. Make sure you place the code on the worksheet where the result is suppose to go not on the daily sheet.
Then runit, then check that the formula result corresponds to the expected result.

Any question, just post back:
Code:
``````Sub copydown()
On Error GoTo erreurs
'clear rows 5 to 576 in column 3
Cells(5, 3).Offset(576, 0).Clear
'rows where to copy
y = 0
For n = 5 To 576 Step 11
'formula to copy over 10 cells
For x = 0 To 9
Cells(n + x, 3).Formula = "=INDEX(DAILY!C:C,ROW()+" & y & ")+INDEX(DAILY!C:C,ROW()+" & y + 11 & ")+INDEX(DAILY!C:C,ROW()+" & y + 22 & ")+INDEX(DAILY!C:C,ROW()+" & y + 33 & ")+INDEX(DAILY!C:C,ROW()+" & y + 44 & ")+INDEX(DAILY!C:C,ROW()+" & y + 55 & ")+INDEX(DAILY!C:C,ROW()+ " & y + 66 & ")"
Next
'increment by 66
y = y + 66
Next
'copy the sum formula from row 15 down
For x = 15 To 576 Step 11
Cells(x, 3).Formula = "=sum(c" & x - 10 & ":c" & x - 1 & ")"
Next
Exit Sub
erreurs:
MsgBox Err.Description
Resume Next
End Sub``````

Replies
3
Views
315
Replies
5
Views
289
Replies
6
Views
135
Replies
8
Views
104
Replies
3
Views
146

### Forum statistics

1,203,109
Messages
6,053,563
Members
444,673
Latest member ### 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.

### Which adblocker are you using?    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

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