# Thread: Copying Formulae with skipped cells

1. ## Copying Formulae with skipped cells

We have timesheet spreadsheets that total the weekly hours on a separate sheet "=SUM('Daily Hours'!F525:F531)" and the cell row reference increments by 7 rows in order to get the totals for each week.
We're now faced with extending last year's timesheets for next year.
Is there a clever way of copying a formula so the cell reference automatically increases by a defined step other than 1?
All we can see to do is make it increment by 1 day so we have 7 times as many rows as we want and then delete the 6 excess rows!

2. ## Re: Copying Formulae with skipped cells

If your sheet is setup as a database, you could try creating a Pivot Table report
In that report, use the date as a column field

Then right click on the date column header and click on Group & Outline, Group...

Select the 'Group by' Days option, and increase the number of days to 7
Also, if you want, you can set the starting and ending dates to correspond with Week-start & -end dates

Hope this helps

3. ## Re: Copying Formulae with skipped cells

Assuming your first sum formula is on row 2 and starts with the range F525:F531, use this formula:

=SUM(OFFSET('Daily Hours'!F\$525,(row()-2)*7,0,7,1))

4. ## Re: Copying Formulae with skipped cells

You can use a VBA Procedure like the following:

<pre>Public Sub CreateFormula()
Dim I As Integer
For I = 0 To 10
Cells(I + 1, 1).Formula = "=Sum('Daily Hours'!F" _
& 525 + I * 7 & ":F" & 531 + I * 7 & ")"
Next I
End Sub
</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•