Dynamic Reference Cell in Offset Formula
Hi,
Not sure if this will help but this will return Jan05-Jan07:
=TEXT(EDATE(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),-24),"mmmyy")&"-"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,"mmmyy")
or if you need to sum values between those 2 dates in column B then:
=SUMPRODUCT((A1:A10=EDATE(DATE(YEAR(TODAY()),MONT H(TODAY())-1,1),-24))*(A1:A10<=DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1),B1:B10)
HTH
Jean-Guy
"MarkM" wrote:
Can I have a dynamic reference cell in my offset formula? What I would like
to do is have my offset formula select the last 24 months from my list of
months in col. A. So when the next month is added my range will change.
For example, in Jan. 07, I would like my offset formula to return the months
for Jan05-Jan07. Then for Feb. 07 I would like it to return Feb05-Feb07, for
march return March 05-march 07 and so on.
|