Yet another array problem

[DonLogan]

Could use some help with this matrix problem. I'm continuing to try,
too. Just looking for a leg up.

I'd like to have a spreadsheet with 6 worksheets
worksheet 1 is Focus
worksheets 2 - 6 are Experiments - Experiments 1 thru Experiments 5 .
The Experiments, and Focus, contain values in cells A1 thru E 15.

The values contained in worksheet Focus's a1, thru e15, are the
largest values in the corresponding cells within the 5 worksheets -
Experiment 1 thru Experiment 5.
example
e1 - a1 = 5
e2 - a1 = 8
e3 - a1 = 9
e4 - a1 = 11
e5 - a1 = 14
so F1 a1 = 14

Thanks in advance.

 

[Sandy Mann]

=MAX('Experiment1'A1,'Experiment2'A1,'Experiment3'A1,'Experiment4'A1,'Experiment5'A1)

copy on the fill handle along to E1 & down to Row 15

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Coderre]

If sheets e1 through e5 are contiguous, in any order....
simply reference the first through the last in your formula

Example:
With sheet in this order: e4___e1___e2___e5___e3

This formula will return the maximum value of cell A1 in those sheets:
=MAX('e4:e3'!A1)

If the sheets are in order, then this:
=MAX('e1:e5'!A1)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[DonLogan]

Thanks folks.
Now part 2, if you don't mind.
Same deal, Focus & worksheets E1 thru E5
E's have values in cells a1 thru f15
But now I need
Focus a1 to contains the smallest value in any of the E sheets
and b1 to contain what worksheet that value came from
and Focus a2 to contain the second lowest, b2 contain worksheet came
from
and so on up to a10, b10

From what you've told me I can do Focus a1 as
=MIN(E1!a1:f15,E2!a1:f15......

but what next

thanks again