Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MIN across worksheets, with INDIRECT
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MIN across worksheets, with INDIRECT
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MIN across worksheets, with INDIRECT
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! "Mike H" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MIN across worksheets, with INDIRECT | Excel Discussion (Misc queries) | |||
Sum across worksheets with indirect | Excel Discussion (Misc queries) | |||
Use of Indirect on two worksheets | Excel Worksheet Functions | |||
INDIRECT.EXT problem with missing worksheets | Excel Worksheet Functions | |||
INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions |