Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
In a worksheet cell, the following works, but when copied into a name, it
returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
Hi,
I managed to get this to work on and off, which is a starting point: name this=INDIRECT(CELL("address")) and then offset(this,0-2) but it kept on switching on and off. So the other way to do it is a macro with Function myoffset(offsetrow As Long, offsetcolumn As Long) As Variant myoffset = Application.Caller.Offset(offsetrow, offsetcolumn).Value End Function -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "dmz_asdf" wrote: In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
I'm not sure why that doesn't work. At first I thought it may be becasue of
the use of the EOMONTH function which is from the ATP. However, I rewrote the formula without the EOMONTH function (you really don't need it) but it still failed. But, all is not lost! This works: Name: LastDate Refers to: =DATE(INDIRECT("RC[-1]",FALSE),INDIRECT("RC[-2]",FALSE)+1,0) Format the result as DATE. The INDIRECT RC stuff is doing the exact same thing as your OFFSET(ADDRESS. Biff "dmz_asdf" wrote in message ... In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
Hi Biff
You could also have a non-volatile solution with Index which would function in the same way. Place your cursor in cell C1 Insert Name Lastdate Refers to =DATE(INDEX(B:B,ROW()),INDEX(A:A,ROW())+1,0) -- Regards Roger Govier "T. Valko" wrote in message ... I'm not sure why that doesn't work. At first I thought it may be becasue of the use of the EOMONTH function which is from the ATP. However, I rewrote the formula without the EOMONTH function (you really don't need it) but it still failed. But, all is not lost! This works: Name: LastDate Refers to: =DATE(INDIRECT("RC[-1]",FALSE),INDIRECT("RC[-2]",FALSE)+1,0) Format the result as DATE. The INDIRECT RC stuff is doing the exact same thing as your OFFSET(ADDRESS. Biff "dmz_asdf" wrote in message ... In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
Or without the extra calls with C1 selected, Insert Name LastDate
Refers to: =DATE(!B1,!A1+1,0) Remove the !'s to make the formula refer only to the active sheet. Roger Govier wrote: Hi Biff You could also have a non-volatile solution with Index which would function in the same way. Place your cursor in cell C1 Insert Name Lastdate Refers to =DATE(INDEX(B:B,ROW()),INDEX(A:A,ROW())+1,0) -- Regards Roger Govier "T. Valko" wrote in message ... I'm not sure why that doesn't work. At first I thought it may be becasue of the use of the EOMONTH function which is from the ATP. However, I rewrote the formula without the EOMONTH function (you really don't need it) but it still failed. But, all is not lost! This works: Name: LastDate Refers to: =DATE(INDIRECT("RC[-1]",FALSE),INDIRECT("RC[-2]",FALSE)+1,0) Format the result as DATE. The INDIRECT RC stuff is doing the exact same thing as your OFFSET(ADDRESS. Biff "dmz_asdf" wrote in message ... In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
Quite right Lori.
I was still overcomplicating things. -- Regards Roger Govier "Lori" wrote in message oups.com... Or without the extra calls with C1 selected, Insert Name LastDate Refers to: =DATE(!B1,!A1+1,0) Remove the !'s to make the formula refer only to the active sheet. Roger Govier wrote: Hi Biff You could also have a non-volatile solution with Index which would function in the same way. Place your cursor in cell C1 Insert Name Lastdate Refers to =DATE(INDEX(B:B,ROW()),INDEX(A:A,ROW())+1,0) -- Regards Roger Govier "T. Valko" wrote in message ... I'm not sure why that doesn't work. At first I thought it may be becasue of the use of the EOMONTH function which is from the ATP. However, I rewrote the formula without the EOMONTH function (you really don't need it) but it still failed. But, all is not lost! This works: Name: LastDate Refers to: =DATE(INDIRECT("RC[-1]",FALSE),INDIRECT("RC[-2]",FALSE)+1,0) Format the result as DATE. The INDIRECT RC stuff is doing the exact same thing as your OFFSET(ADDRESS. Biff "dmz_asdf" wrote in message ... In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
=DATE(!B1,!A1+1,0)
That's got major bugs! We had a rather in-depth discussion a month or two ago about referring to sheets like !B1. Too many bugs! Try testing it with and without the ! on various sheets. Biff "Lori" wrote in message oups.com... Or without the extra calls with C1 selected, Insert Name LastDate Refers to: =DATE(!B1,!A1+1,0) Remove the !'s to make the formula refer only to the active sheet. Roger Govier wrote: Hi Biff You could also have a non-volatile solution with Index which would function in the same way. Place your cursor in cell C1 Insert Name Lastdate Refers to =DATE(INDEX(B:B,ROW()),INDEX(A:A,ROW())+1,0) -- Regards Roger Govier "T. Valko" wrote in message ... I'm not sure why that doesn't work. At first I thought it may be becasue of the use of the EOMONTH function which is from the ATP. However, I rewrote the formula without the EOMONTH function (you really don't need it) but it still failed. But, all is not lost! This works: Name: LastDate Refers to: =DATE(INDIRECT("RC[-1]",FALSE),INDIRECT("RC[-2]",FALSE)+1,0) Format the result as DATE. The INDIRECT RC stuff is doing the exact same thing as your OFFSET(ADDRESS. Biff "dmz_asdf" wrote in message ... In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formula with Dynamic Address
That only works on one sheet. If you enter it on another sheet it refers
back to the original sheet. As soon as you create the name and use the formula Excel adds the sheet making it absolute to the original sheet. Biff "Roger Govier" wrote in message ... Hi Biff You could also have a non-volatile solution with Index which would function in the same way. Place your cursor in cell C1 Insert Name Lastdate Refers to =DATE(INDEX(B:B,ROW()),INDEX(A:A,ROW())+1,0) -- Regards Roger Govier "T. Valko" wrote in message ... I'm not sure why that doesn't work. At first I thought it may be becasue of the use of the EOMONTH function which is from the ATP. However, I rewrote the formula without the EOMONTH function (you really don't need it) but it still failed. But, all is not lost! This works: Name: LastDate Refers to: =DATE(INDIRECT("RC[-1]",FALSE),INDIRECT("RC[-2]",FALSE)+1,0) Format the result as DATE. The INDIRECT RC stuff is doing the exact same thing as your OFFSET(ADDRESS. Biff "dmz_asdf" wrote in message ... In a worksheet cell, the following works, but when copied into a name, it returns #VALUE since the INDIRECT fails. =EOMONTH(DATE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN ())),0,-1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),1),0) Column 1 has month values Column 2 has year values Column 3 has the formula I thought this would be a valid extrapolation of dynamic ranges, but perhaps I should be looking into something else. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Function/ formula to output a cell reference | Excel Worksheet Functions | |||
Dynamic (?) formula | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |