Column Reference Copy

[Jon C]

Hi,

I have a sheet with sequential dates in the columns and names on the rows,
at the intersect is a number of hours. It'd like this totaled, on a
seperate sheet by weeks in the columns, names in rows as before. Dead easy,
just sum column B to H (7 of 'um) then copy down for each name. Problem is
when I copy across I get C to I, D to J, etc! There may be a pivot table
approach to thsi but the sheet neesd to be as automatic as possible. Any
ideas?

Thanks,

Jon C

 

[Bernie Deitrick]

Jon,

When you have a formula like =SUM(B1:H2), select the B1:H2 part, and press
F4 until it looks like $B1:$H2

Then when you copy across, you won't get C to I, D to J, etc.

If that doesn't make sense, then post a small sample of your data, and the
formula that you are actually using.

HTH,
Bernie
MS Excel MVP

 

[Jon C]

Hi Bernie,

This won't work.

The resultantant formula, keyed long hand, I'm looking for should be
Sum(B2:H2) in the first weeks column i.e. summing days 1 to 7, Sum (I2:O2)
in the next i.e. days 8 to 14, Sum(P2:V2) i.e.15 to 21, etc.

My source data has lots of dates so I'm looking for a way, via a function or
a clever drag, to automatically copy the weekly totals formulas across my
sheet.

Does this make more sense?

Thanks,

Jon C

 

[Bernie Deitrick]

OK, Jon, that's a better explanation and makes sense. To do that, use the
formula:

=SUM(OFFSET($A2,0,(COLUMN()-COLUMN($XXX$YYY))*7+1,1,7))

Substitute the column letter and row number of the first cell where your
enter this formula for the XXX and YYY-- so if you enter this formula into
cell B8 and want to copy it to C8, D8, etc, the $XXX$YYY should be $B$8.

HTH,
Bernie
MS Excel MVP

 

[Jon C]

Excellent. Thanks Bernie,just wish I understood what the hec it does...

Jon C

 

[Bernie Deitrick]

Jon,

Here's how it works.

Let's say that you put the formula into cell B8:

=SUM(OFFSET($A2,0,(COLUMN()-COLUMN($B$8))*7+1,1,7))

The (COLUMN()-COLUMN($B$8))*7+1 part evaluates to

(2-2)*7+1 which finally equals 1, so you get:

=SUM(OFFSET($A2,0,1,1,7))

which means sum the cells starting 1 column over from A2, and extending for
7 columns (or cells B2:H2)

When you copy it to cell C8, the

(COLUMN()-COLUMN($B$8))*7+1 part evaluates to

(3-2)*7+1 which equals 8, so you get:

=SUM(OFFSET($A2,0,8,1,7))

which means sum the cells starting 8 columns over from A2, and extending for
7 columns (or cells I2:O2)

And so on....

HTH,
Bernie
MS Excel MVP