Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 694
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find value in array Brook6 Excel Worksheet Functions 26 January 30th 07 10:40 PM
Help with copying dynamic column selected based on remote cell value and dynamic formula fill ers Charts and Charting in Excel 0 March 1st 06 02:05 AM
Function/ formula to output a cell reference Creator Excel Worksheet Functions 19 February 17th 06 07:39 PM
Dynamic (?) formula Thore Excel Worksheet Functions 2 February 9th 05 06:25 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 12:29 PM


All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"