Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australia, Australian Capital Territory, Australia
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    HELP PLEASE.

  2. #2
    zenood
    Guest

    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. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Formulae with skipped cells

    To your question:

    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))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

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