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