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

 

[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).