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

[JoshuaMars]

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.

Thanks in advance.

Joshua

 

[sunnyland]

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:

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