Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions

Hello -

I propose that a RANGE() function be added that is equivalent to the
existing ADDRESS() function or a shorthand equivalent to
CONCATENATE(ADDRESS(),":",ADDRESS()). Indirect addressing is useful when
dealing with real-world data of variable extent or quality.

I further propose that a notation such as ADDRESS({$A$10},{$A$10}) and
ADDRESS({{$A$10}}) be allowed as equivalent to
ADDRESS(ROW($A$10),COLUMN($A$10)) for additional notation simplification when
specifying indirect addresses or address ranges. The second form above would
be applicable as part of a variable RANGE() function where one point is
fixed. If all points are fixed, then there is no point in using an indirect
range or address.

Note that using sheet cell references allows these formulas to reflect
changes in sheet structure (add/remove columns or rows) as long as those
references are at the corners of the intended range and are not deleted by
these operations.

Thanks.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I expect Excel might, one day, catch up with this functionality that Lotus
123 has had for years and years. However, we have to bear in mind that "..
Excel is not a Lotus 123 clone ..".

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Mike Barlow" <[email protected]> wrote in message
news:[email protected]...
> Hello -
>
> I propose that a RANGE() function be added that is equivalent to the
> existing ADDRESS() function or a shorthand equivalent to
> CONCATENATE(ADDRESS(),":",ADDRESS()). Indirect addressing is useful when
> dealing with real-world data of variable extent or quality.
>
> I further propose that a notation such as ADDRESS({$A$10},{$A$10}) and
> ADDRESS({{$A$10}}) be allowed as equivalent to
> ADDRESS(ROW($A$10),COLUMN($A$10)) for additional notation simplification
when
> specifying indirect addresses or address ranges. The second form above
would
> be applicable as part of a variable RANGE() function where one point is
> fixed. If all points are fixed, then there is no point in using an
indirect
> range or address.
>
> Note that using sheet cell references allows these formulas to reflect
> changes in sheet structure (add/remove columns or rows) as long as those
> references are at the corners of the intended range and are not deleted by
> these operations.
>
> Thanks.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/office/comm...heet.functions

"Andy Wiggins" <[email protected]> wrote...
>I expect Excel might, one day, catch up with this functionality
>that Lotus 123 has had for years and years. However, we have to
>bear in mind that "..Excel is not a Lotus 123 clone ..".
....

?

I use 123 and Excel equally, so I think I know what each provides. What
feature did the OP mention that 123 has?

That said, 123's @@("<<"&path&filename&">>"&rangeref) works just fine on
closed workbooks, and @@("<<?>>"&SomeRangeNameHere) is hugely useful. But
@COORD and @REFCONVERT aren't that big a deal.

"Mike Barlow" <[email protected]> wrote...
>I propose that a RANGE() function be added that is equivalent to
>the existing ADDRESS() function or a shorthand equivalent to
>CONCATENATE(ADDRESS(),":",ADDRESS()). Indirect addressing is
>useful when dealing with real-world data of variable extent or
>quality.

How INDIRECT has anything to do with variable data quality is at best
unclear.

INDIRECT(ADDRESS(u,v)&ADDRESS(x,y)) is always a mistake. There's a way to do
this using OFFSET($A$1,u-1,v-1,x-u+1,y-v+1).

How many wheels must Microsoft reinvent?

>I further propose that a notation such as ADDRESS({$A$10},{$A$10})
>and ADDRESS({{$A$10}}) be allowed as equivalent to
>ADDRESS(ROW($A$10),COLUMN($A$10)) for additional notation
>simplification when specifying indirect addresses or address
>ranges. The second form above would be applicable as part of a
>variable RANGE() function where one point is fixed. If all
>points are fixed, then there is no point in using an indirect
>range or address.

And once you learn OFFSET you'll find there's never a need for
INDIRECT(ADDRESS(..)).

That said, CELL("Address",$A$10) returns the string "$A$10". Again, how many
wheels must Microsoft reinvent?

Hello

An indirect range could be used inside a MATCH() statement and any other
statement that might use a variable range specification. As an example:
[M3]=M2+MATCH(TRUE,INDIRECT(CONCATENATE(ADDRESS(M2+1,COLUMN($L$2)),":",ADDRESS($B$7,COLUMN($L$2)))),0)
copied down to create a sequential table of offsets or pointers to rows
containing valid data where $B$7 stores the (data-set dependant) last active
row number and column $L$* contains the data validity tests. After the last
valid (TRUE) row is found, the function returns NA!. M2 points to the first
valid row.

As far as I can tell, the CELL() function cannot be used to create a
dynamic indirect range. My first proposal is to provide a RANGE() function
for applications where a variable, data-dependent range specification might
be required and my second proposal was to allow a shorthand notation for the
ROW() and COLUMN() functions in situations where row or column numbers are
the required values.

My proposal is just that. It is a tribute to EXCEL that such complex
functions can be created with the program as it is now.

"Harlan Grove" wrote:

> "Mike Barlow" <[email protected]> wrote...
> >I propose that a RANGE() function be added that is equivalent to
> >the existing ADDRESS() function or a shorthand equivalent to
> >CONCATENATE(ADDRESS(),":",ADDRESS()). Indirect addressing is
> >useful when dealing with real-world data of variable extent or
> >quality.
>
> How INDIRECT has anything to do with variable data quality is at best
> unclear.
>
> INDIRECT(ADDRESS(u,v)&ADDRESS(x,y)) is always a mistake. There's a way to do
> this using OFFSET($A$1,u-1,v-1,x-u+1,y-v+1).
>
> How many wheels must Microsoft reinvent?
>
> >I further propose that a notation such as ADDRESS({$A$10},{$A$10})
> >and ADDRESS({{$A$10}}) be allowed as equivalent to
> >ADDRESS(ROW($A$10),COLUMN($A$10)) for additional notation
> >simplification when specifying indirect addresses or address
> >ranges. The second form above would be applicable as part of a
> >variable RANGE() function where one point is fixed. If all
> >points are fixed, then there is no point in using an indirect
> >range or address.
>
> And once you learn OFFSET you'll find there's never a need for
> INDIRECT(ADDRESS(..)).
>
> That said, CELL("Address",$A$10) returns the string "$A$10". Again, how many
> wheels must Microsoft reinvent?
>
>
>
>
>

"Mike Barlow" <[email protected]> wrote...
>An indirect range could be used inside a MATCH() statement and any other
>statement that might use a variable range specification. As an example:

>[M3]=M2+MATCH(TRUE,INDIRECT(CONCATENATE(ADDRESS(M2+1,
>COLUMN($L$2)),":",ADDRESS($B$7,COLUMN($L$2)))),0)
....

Again, USE OFFSET!

=MATCH(TRUE,OFFSET($A$1,M2,COLUMN($L$2)-1,$B$7-M2,1),0)

INDIRECT(ADDRESS(.)) or INDIRECT(ADDRESS(.)&":"&ADDRESS(.)) is *ALWAYS* a
mistake and demonstrates a fundamental lack of understanding of how to use
Excel efficiently.

>As far as I can tell, the CELL() function cannot be used to create a
>dynamic indirect range. . . .

So? In your original proposal you had mooted the following.

>>I further propose that a notation such as ADDRESS({$A$10},{$A$10})
>>and ADDRESS({{$A$10}}) be allowed as equivalent to
>>ADDRESS(ROW($A$10),COLUMN($A$10))

This had nothing whatsoever to do with your proposed RANGE function. I was
just pointing out that your proposed ADDRESS({{$A$10}}) is unnecessary since
it's already provided by CELL("Address",$A$10) or CELL("Address",A10). Just
see what you get from

B2
=CELL("Address",C5)&":"&CELL("Address",F10)

Now, as for the underlying reasons this (mercifully) won't happen, curly
braces are already part of the syntax, used to delimit array constants.
While operator, or in this case delimiter, overloading isn't completely out
of the question, Microsoft is very unlikely to complicate Excel's formula
syntax to that degree. Restricting this just to ADDRESS would require that
ADDRESS be parsed specially, apart from other functions. That's almost
certainly not going to happen.

> . . . My first proposal is to provide a RANGE() function
>for applications where a variable, data-dependent range specification
>might be required and my second proposal was to allow a shorthand
>notation for the ROW() and COLUMN() functions in situations where row
>or column numbers are the required values.

RANGE is unnecessary because INDIRECT(ADDRESS(.)) is always a mistake. The
OFFSET function already exists to provide the functionality you claim to
seek. It's obvious you're unfamiliar with it. Your time would be better
spent learning how to use it than on pipe dreams for redundant, unnecessary
new functionality that wouldn't be likely to make it into Excel for a
decade. (Yes, a decade - it took Microsoft almost 10 years from when then
Borland introduced colored worksheet tabs in Quattro Pro to make them a
feature in Excel 2002. What you're asking for is a heck of a lot harder to
implement.)

>My proposal is just that. It is a tribute to EXCEL that such complex
>functions can be created with the program as it is now.

And a further tribute that there are already functions in Excel that make
certain complex formula constructs unnecessary, such as
INDIRECT(ADDRESS(.)).

That said, some proposals are fine. Others deserve to be shot down in
flames.

Thanks Harlan

I have been assuming that the OFFSET() function returned a value instead
of a potential range reference and I quite glossed over the height and width
parameters. I assume from your comments that the INDIRECT([constructed
string]) method is rather inefficient for the purpose intended. I thought so
too. For my application, the only change required of your formula is to add
each previous value (M2 + MATCH(...)) to obtain a vector table referenced to
the head to the head of the column. Hopefully this exchange has enlightened
others to this application possibility.

MLB