complicated... not for the faint hearted

[mike]

morning all.
this is a little complicated, i'll explain the situation.
i have a workbook, two worksheets, one for 2005, one for 2004.
there are 13 columns in each sheet, one column in each sheet relates to an
amount, and another column relates to a date.
so far i have managed to combine the sheets to sum the amount column and if
it reaches a certain amount, show a message to that effect.
the problem is that the way it is currently set up it takes amounts from
2004 and adds them to 2005 ytd.
what i really would like to be able to do is have a rolling twelve months.
i know that i need to link the date in there somewhere specifically in
mm/yyyy format.
if anybody has any suggestions on the code that i would use to do this, they
would be greatly appreciated.
thanks

mike

[Ankit Wadhawan]

> the problem is that the way it is currently set up it takes amounts from
> 2004 and adds them to 2005 ytd.

What exactly you want to do?
Can you give a little more detail?


To Link a column with a date you can put MM and YYYY in different columns.

You can use "Validation" for assigning pre-defined values to those column


Regards,
Ankit



"mike" <mike@discussions.microsoft.com> wrote in message
news:2F32196D-1F7C-4BE4-8BA2-D3BCE925734B@microsoft.com...
> morning all.
> this is a little complicated, i'll explain the situation.
> i have a workbook, two worksheets, one for 2005, one for 2004.
> there are 13 columns in each sheet, one column in each sheet relates to an
> amount, and another column relates to a date.
> so far i have managed to combine the sheets to sum the amount column and
> if
> it reaches a certain amount, show a message to that effect.
> the problem is that the way it is currently set up it takes amounts from
> 2004 and adds them to 2005 ytd.
> what i really would like to be able to do is have a rolling twelve months.
> i know that i need to link the date in there somewhere specifically in
> mm/yyyy format.
> if anybody has any suggestions on the code that i would use to do this,
> they
> would be greatly appreciated.
> thanks
>
> mike

[bpeltzer]

I take it you've currently got an equation such ash
=sum('2005'!a:a)+sum('2004'!a:a), but the 2004 portion should be conditional
on the date. You can do that with SUMIF:
=sum('2005'!a:a)+sumif('2004'!b:b,">"&today()-365,'2004'!a:a). You can
adjust the start date as needed. For instance if you want ALL of November
'04 even though today is late in the month, it might be
">="&date(2004,month(today()),1).

"mike" wrote:

> morning all.
> this is a little complicated, i'll explain the situation.
> i have a workbook, two worksheets, one for 2005, one for 2004.
> there are 13 columns in each sheet, one column in each sheet relates to an
> amount, and another column relates to a date.
> so far i have managed to combine the sheets to sum the amount column and if
> it reaches a certain amount, show a message to that effect.
> the problem is that the way it is currently set up it takes amounts from
> 2004 and adds them to 2005 ytd.
> what i really would like to be able to do is have a rolling twelve months.
> i know that i need to link the date in there somewhere specifically in
> mm/yyyy format.
> if anybody has any suggestions on the code that i would use to do this, they
> would be greatly appreciated.
> thanks
>
> mike