Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Can I copy a row to a column? | New Users to Excel | |||
How do you copy a cell formula down a column without displaying n. | Excel Worksheet Functions | |||
how to copy 2350 hyperlink full paths to any column in a worksheet ? | Excel Discussion (Misc queries) | |||
3-d reference not adjusting when using Fill Handle to copy down | Excel Worksheet Functions |