Dynamic Formula with Dynamic Address

D

dmz_asdf

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

Martin Fishlock

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

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
 
R

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


T. Valko said:
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 said:
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.
 
L

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.

Roger said:
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 said:
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 said:
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.
 
R

Roger Govier

Quite right Lori.

I was still overcomplicating things.

--
Regards

Roger Govier


Lori said:
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 said:
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 said:
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

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

Lori said:
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 said:
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 said:
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

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

Roger Govier said:
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 said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top