hello again... complicated macro using dates
 

hello me again.
i've had a look at previous posts but am still a bit stuck!

i have a workbook.
there are two worksheets.
one is for 2004 and one is for 2005, soon I will have 2005 and 2006, but we
can talk about that later.
there are various columns.
i am particularly looking at two... date column and amount column.
the date column does not run sequentially, the dates are ad hoc, eg, they do
not run 21/22/23 it may be 20/22/23/27.
i have a macro which will add the values in the amount columns from both
sheets.
but what i would like it to do is...
look at today's date, and find the corresponding date in the 2004 sheet and
add everything from that date on, so it is effectively a 12 month rolling
period.
any suggestions would be greatly appreciated.
thanks in advance

mike

hello again... complicated macro using dates
 

The SUMIF worksheet function should be able to do this - for the prior year's
sheet it would be (substituting in the proper addresses for the date range
and amount range):
=SUMIF(DateRange,"="&TEXT(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),"mm/dd/yy"),AmountRange)
I assume you can figure the total from the current year, since presumably
all amounts are before today's date, but if not the form of the formula would
be the same.
--
- K Dales


"mike" wrote:

hello me again.
i've had a look at previous posts but am still a bit stuck!

i have a workbook.
there are two worksheets.
one is for 2004 and one is for 2005, soon I will have 2005 and 2006, but we
can talk about that later.
there are various columns.
i am particularly looking at two... date column and amount column.
the date column does not run sequentially, the dates are ad hoc, eg, they do
not run 21/22/23 it may be 20/22/23/27.
i have a macro which will add the values in the amount columns from both
sheets.
but what i would like it to do is...
look at today's date, and find the corresponding date in the 2004 sheet and
add everything from that date on, so it is effectively a 12 month rolling
period.
any suggestions would be greatly appreciated.
thanks in advance

mike