Dynamic Formula with Dynamic Address

[Guest]

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.

 

[Guest]

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

 

[T. Valko]

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

 

[Roger Govier]

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

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

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.

 

[Lori]

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.

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

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

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.

 

[Roger Govier]

Quite right Lori.

I was still overcomplicating things.

--
Regards

Roger Govier

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.

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

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

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.

 

[T. Valko]

=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

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.

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

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

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.

 

[T. Valko]

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

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

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

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.