OFFSET function to pick up monthly groups of data

[Gary T]

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.

 

[David Jessop]

Hi,

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

Regards,

David Jessop

 

[Gary T]

Not to worry, I got it:


In cell C6 I put:

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

Gart T.