MIN across worksheets, with INDIRECT

[andy62]

Trying this again, with correction and clarification:

In the formula below, "$B$3" is a reference to the current worksheet which
contains a whole number to be used as a row reference on other worksheets.
This formula works when I pass a single-worksheet range to MIN, but not when
I pass a multi-sheet reference:

=MIN(INDIRECT("Rater1:Rater4!C"&$B$3))

Is there another way to type it, or do I need a more creative function?

TIA

 

[Mike H]

Hi,

I assume rater is sheet in your language? Try this

=MIN(N(INDIRECT("Rater" & ROW(1:4) & "!C"&B3)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[andy62]

Hi Mike - Thanks, that seems to work. I like the use of the ROWS function,
not sure why the N function is needed. But I'm not complaining! Thanks!