View Single Post
  #6   Report Post  
Mike Barlow
 
Posts: n/a
Default

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?