Indirect(Address(...

[Guest]

Address(5,$Z$5+60) appears to refer to the cell I want; however, I'm trying
to use the Address function inside a Rank function and have tried it with and
without the Indirect function (as shown below) and it doesn't work -- any
thoughts?

=Rank($BE$5,Indirect(Address(5,$Z$5+60)):Indirect( Address(1000,$Z$5+60)))

The range always comes back as 0.

Thanks

 

[Jim Rech]

Indirect and Offset may or may not work the way you want but I think OFFSET
is a simpler way to go.

If Z5 is 0 the range you would want returned by the Indirect and Offset
portions of the formula is $BH$5:$BH$1000

Therefore to get the range offset from that by the number of columns in Z5
try this:

OFFSET($BH$5:$BH$1000,,$Z$5)

--
Jim
| Address(5,$Z$5+60) appears to refer to the cell I want; however, I'm
trying
| to use the Address function inside a Rank function and have tried it with
and
| without the Indirect function (as shown below) and it doesn't work -- any
| thoughts?
|
| =Rank($BE$5,Indirect(Address(5,$Z$5+60)):Indirect( Address(1000,$Z$5+60)))
|
| The range always comes back as 0.
|
| Thanks