Copying Relative Cell Referencing

**BellaGee** · 06-20-2013, 04:08 AM

Hi there,

I have a problem with my excel spreadsheet...

I would like to report on figures for a week period in a report. I have one tab which has each day running along the top, and under each day the number of instances of something is recorded - e.g. Meetings, or Actions.

I then have a separate table which sums the information for each weekly period.

How do I structure this formula so that when I copy the formula along it automatically goes to the 7 cells after the previous formula?

E.g.

Summary Week 1= Sum(B3:H3)
Summary Week 2 = Sum(I3:O3)
Summary Week 3 = Sum(P3:V3)

(These are actually in adjacent cells but easier to illustrate this way)

When I have tried copying the formula across I end up with
Summary Week 1= Sum(B3:H3)
Summary Week 2 = Sum(C3:I3)
Summary Week 3 = Sum(D3:J3)

This is a pretty crude example; I have tried a few variations but I keep getting the same result - instead of referencing the 7 cells after it is referencing the ones that are immediately next.

Have attached an example of what I am trying to achieve here - any help on this would be greatly appreciated

**Ace_XL** · 06-20-2013, 04:23 AM

In B10

=SUM(INDEX($B3:$AC3,,COLUMN(A$1)*7-6):INDEX($B3:$AC3,,COLUMN(A$1)*7))

Copy down and across

**BellaGee** · 06-20-2013, 04:31 AM

Wow - thank you

I'm going to have to do a little research into this formula so I can understand it and know how to use it next time, but that is exactly what I needed and it works perfectly. Like magic