INDIRECT(ADDRESS(...)) in ranges

I need to find the minimum value of a range which starts in C10 and ends n rows below. The number n is the result of a counter located in A1. I would expect following formula to work:
MIN(C10:ADDRESS(($A$1+9),3))
but it doesn't. I must write:
MIN(C10:INDIRECT(ADDRESS(($A$1+9),3)))
and I find that strange, because the INDIRECT-statement means the content of lowermost cell in the range and not the cell itself

Can anyone explain why it works this way?

The result shall appear in Sheet 'Dist' whith the raw data in sheet 'ChanA'. When I know the limits of the range I can write in sheet 'Dist':
=MIN(ChanA!C10:C73)
and that works perfectly. But my data varies and I have built in a counter in A1 to give the limit of the range, so I try with:
=MIN(ChanA!C7:INDIRECT(ADDRESS($A$1+9,3)))
but it results in an error.
The same value is in A1 in both sheets.
What can I do about it?


NSV

When you use the Indirect() function like you have (within a range referencing formulation), then Indirect() returns a reference rather than a value.... see Index help files for more....not sure why Indirect() help doesn't mention this clearly.

Actually, using Index() to do what you want is prefered as it is not volatile like Indirect().

Try changing your formula to: =MIN(C10:INDEX(C:C,$A$1+9))

and your other formula should include the sheet reference in the Indirect() or Index() function too.

e.g. =MIN(ChanA!C7:INDEX(ChanA!C:C,$A$1+9))

Thanks for the tip about INDEX() - it works perfectly.

INDIRECT() is apparently not as straightfoward as one should think. Still I wonder why it is necessary at all. Logically it should do with just MIN(C10:ADDRESS($A$1+9),3)
If I in any other cell write ADDRESS($A$1+9,3) and the counter in A1 indicates eg. 64 inputs, then this cell will display $C$73, which is the bottom of the range. It ought to fit into the MIN(...) formula, but it just doesn't.


NSV