"Advanced" formula dragging

[jefflebowski]

My spreadsheet has multiple worksheets. The data I enter into sheet 2 (named data) is summarized on sheet 1, for example. A formula looks like:

Please Login or Register  to view this content.

Every week, I drag the entire row of formulas down so I can summarize the new week. But I have to manually adjust each formula to reference the correct seven cells. The drag function changes the formula to

Please Login or Register  to view this content.

but it needs to be

Please Login or Register  to view this content.

And of course each subsequent summary week on sheet 1 needs to reflect the NEXT seven days of data from sheet 2.

ctrl+f is fine obviously, but if I didn't have to make the changes manually...

I tried to search the forum for this, but I don't know what term to use other than 'drag formula'. And I know how to do that.

[AliGW]

Try this:

=SUM(OFFSET(Data!$J$2:$J$8,7*(ROWS(A$1:A1)-1),0))

[Pete_UK]

If you want to avoid a volatile function (like INDIRECT or OFFSET), you can do this:

=SUM(INDEX('data'!J:J,(rows($1:1)-1)*7+1):INDEX('data'!J:J,rows($1:1)*7))

then copy down as required.

Hope this helps.

[jefflebowski]

Try this:

=SUM(OFFSET(Data!$J$2:$J$8,7*(ROWS(A$1:A1)-1),0))

YES!

Success.

[AliGW]

Glad to have helped!