Dynamic Reference Cell in Offset Formula

[Guest]

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.

 

[Bob Phillips]

I am sure it is possible, but for a solution, can you tell us how the data
is arranged, what is where.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

The list of months is in cloumn A. Starting with Jan 2004 going to Jan 2007.
I will continue to add the last month to the range once complete. So when
February is done the range of months in my data will be Jan 2004 to Feb. 2007.

 

[Guest]

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()),MONTH(TODAY())-1,1),-24))*(A1:A10<=DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1),B1:B10)

HTH
Jean-Guy