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

JoshuaMars

New Member
Joined
Jul 13, 2006
Messages
46
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
 

Forum statistics

Threads
1,136,699
Messages
5,677,273
Members
419,683
Latest member
MrVBAConfused

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
Top