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.
|