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...et.f unctions

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

Hello!

Thank you for your suggestion regarding the addition of a RANGE() function in Excel. I completely understand the need for indirect addressing when dealing with variable data, and I agree that it would be a useful addition to the program.

In the meantime, there are a couple of workarounds that you can use to achieve similar results. One option is to use the CONCATENATE() function to create a string that represents the range you want to reference. For example, if you want to reference a range from A1 to A10, you could use the formula =INDIRECT(CONCATENATE("A1:A10")). This will create a reference to the range A1:A10 that you can use in other formulas.

Another option is to use the ROW() and COLUMN() functions in conjunction with the ADDRESS() function to create a reference to a specific cell. For example, if you want to reference cell A1, you could use the formula =INDIRECT(ADDRESS(1,1)). This will create a reference to cell A1 that you can use in other formulas.

1. Use the CONCATENATE() function to create a string that represents the range you want to reference.
2. Use the ROW() and COLUMN() functions in conjunction with the ADDRESS() function to create a reference to a specific cell.

Let me know if you have any other questions or if there's anything else I can assist you with.

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" wrote in message
...
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...et.f unctions

"Andy Wiggins" 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" 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,C OLUMN($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" 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" 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