Address func in array reference
 

Hi,

I am trying to use ADDRESS(B1-1,1,4,1) in a MIN function, the result
is and should be A307. The sub step I am looking for would be
MIN(ARC!A4:A307)

Such as MIN(ARC!A4:ADDRESS(B1-1,1,4,1))

but I get an error message. Any pointers on how I can make this work?

Thanks,
Joe

Hey Joe,

=MIN(INDIRECT("ARC!A4:"&ADDRESS(B1-1,1,4,1)))

Or, more simply

=MIN(INDIRECT("ARC!A4:A"& B1-1))

HTH,
Bernie
MS Excel MVP

"Joe Blow" wrote in message
...

Hi,

I am trying to use ADDRESS(B1-1,1,4,1) in a MIN function, the result
is and should be A307. The sub step I am looking for would be
MIN(ARC!A4:A307)

Such as MIN(ARC!A4:ADDRESS(B1-1,1,4,1))

but I get an error message. Any pointers on how I can make this work?

Thanks,
Joe

On Mon, 7 Feb 2005 13:11:11 -0500, "Bernie Deitrick" <deitbe @
consumer dot org wrote:

Hey Joe,

=MIN(INDIRECT("ARC!A4:"&ADDRESS(B1-1,1,4,1)))

Or, more simply

=MIN(INDIRECT("ARC!A4:A"& B1-1))

HTH,
Bernie
MS Excel MVP

"Joe Blow" wrote in message
.. .

Hi,

I am trying to use ADDRESS(B1-1,1,4,1) in a MIN function, the result
is and should be A307. The sub step I am looking for would be
MIN(ARC!A4:A307)

Such as MIN(ARC!A4:ADDRESS(B1-1,1,4,1))

but I get an error message. Any pointers on how I can make this work?

Thanks,
Joe


Thanks So Much Bernie!

Your most eloquent solution worked like a charm!
Joe

Bernie Deitrick wrote...
....
Or, more simply

=MIN(INDIRECT("ARC!A4:A"& B1-1))
....

Simpler still,

=MIN(OFFSET(ARC!A4,0,0,B1-3))