Column Reference Copy
 

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

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" wrote in message
...
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

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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

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

Jon C

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

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


"Jon C" wrote in message
...
Excellent. Thanks Bernie,just wish I understood what the hec it
does...:-)

Jon C

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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