Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Reference Cell in Offset Formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Reference Cell in Offset Formula
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) "MarkM" <mark_mattson(at)yahoo.com wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Reference Cell in Offset Formula
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. "Bob Phillips" wrote: 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) "MarkM" <mark_mattson(at)yahoo.com wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need formula to reference a cell in previous worksheet | Excel Worksheet Functions | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
CELL Function: cell reference by formula | Excel Worksheet Functions | |||
Cell reference in different worksheet in formula | Excel Worksheet Functions |