Thread: 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" <JoeBlow@kokomo.com> wrote in message
news:fuaf01h9lqgnm9g7gagpn2jac1vusnev81@4ax.com...
>
> 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" <JoeBlow@kokomo.com> wrote in message
>news:fuaf01h9lqgnm9g7gagpn2jac1vusnev81@4ax.com...
>>
>> 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))