Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Gary T
 
Posts: n/a
Default OFFSET function to pick up monthly groups of data

Hi,

I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from
1-Jan-05 and running through till 11-Sep-05.

In cells A6:A62, I have various cost centres which can be allocated costs,
such as sales, rent, overheads, miscellaneous, etc.

So I therefore have a grid (C6:IV62) in which I can allocate costs against
the appropriate cost centre & date.

In "Weekly!" worksheet, I have the same list of Cost Centres again appearing
in cells A6:A62. However, instead of having daily dates in row 2, I have the
start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,
in cell E2 is 16-Jan-05).

In cell C6, I have the following formula:

=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))

which I can copy across and down and will pick up the weekly totals for each
cost centre (note it starts at "Daily!$D6 as this is the first Sunday in
2005).

However, I also have a "Monthly!" worksheet, which is set up in exactly the
same way, and in which I want to put a formula in C6 which I can copy across
and down to pick up the monthly totals for each cost centre. However, I
can't think how to do it as the number of days in each month is not constant
(because the number of days per week is always 7, the Cols and [width]
parameters in the OFFSET function above were relatively easy to write).

Any help greatly appreciated,

Regards,

Gary T.




  #2   Report Post  
David Jessop
 
Posts: n/a
Default

Hi,

Try using =MATCH on the dates and then use =INDEX (or OFFSET) to get the data?

Regards,

David Jessop

"Gary T" wrote:

Hi,

I have a series of dates in "Daily!" worksheet, cells C2:IV2, starting from
1-Jan-05 and running through till 11-Sep-05.

In cells A6:A62, I have various cost centres which can be allocated costs,
such as sales, rent, overheads, miscellaneous, etc.

So I therefore have a grid (C6:IV62) in which I can allocate costs against
the appropriate cost centre & date.

In "Weekly!" worksheet, I have the same list of Cost Centres again appearing
in cells A6:A62. However, instead of having daily dates in row 2, I have the
start of the week headings (i.e. in C2 is 02-Jan-05, in cell D2 is 09-Jan-05,
in cell E2 is 16-Jan-05).

In cell C6, I have the following formula:

=SUM(OFFSET(Daily!$D6,0,(COLUMN(C$2)-COLUMN($C$2))*7,1,7))

which I can copy across and down and will pick up the weekly totals for each
cost centre (note it starts at "Daily!$D6 as this is the first Sunday in
2005).

However, I also have a "Monthly!" worksheet, which is set up in exactly the
same way, and in which I want to put a formula in C6 which I can copy across
and down to pick up the monthly totals for each cost centre. However, I
can't think how to do it as the number of days in each month is not constant
(because the number of days per week is always 7, the Cols and [width]
parameters in the OFFSET function above were relatively easy to write).

Any help greatly appreciated,

Regards,

Gary T.




  #3   Report Post  
Gary T
 
Posts: n/a
Default

Not to worry, I got it:


In cell C6 I put:

=SUM(OFFSET(Daily!$C7,0,IF(ISNUMBER(B2)=FALSE,0,EO MONTH(B2,0)+1-$C$2),1,(EOMONTH(C$2,0)-C$2+1)))

Gart T.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 06:17 AM
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Should Merging workbooks pick up new data or only edited data? Peggy L. Excel Worksheet Functions 0 January 13th 05 06:31 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
What function or formula do I use to calculate ROI with this data? Dave Excel Worksheet Functions 9 November 5th 04 03:42 PM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"