Address func in array reference

[Joe Blow]

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

 

[Bernie Deitrick]

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]

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

Thanks So Much Bernie!

Your most eloquent solution worked like a charm!
Joe

 

[Harlan Grove]

Bernie Deitrick wrote...
....

Or, more simply

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

....

Simpler still,

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