Cell address with maximun value

Posted by Paul on July 26, 2001 2:48 PM

I can use the formula =CELL("address",INDEX(A1:A10,MATCH(MAX(A1:A10),A1:A10,0))) to find the cell address with the maximun value on one sheet and for only one column, but how would this formula need to be modified to look across different worksheets, I want to find the cell address of the maximum value across three sheets named 1999, 2000, and 2001 for the range B2:M32. Thanks for any help.

Posted by IML on July 27, 2001 12:02 PM

I hope you find an answer, this is a good puzzle. The only thing I could come up with would be to find the max value on one sheet across a couple of columns. More functional then slick, as it involves hidden rows.
Using your range, put =max(B1:B32) in cell B33 and copy to column M.
Put =max(b1:m1)in N1 and copy down to N32

Now put this formula where you want to display the value
=ADDRESS(MATCH(MAX(N1:N32),N1:N32,0),MATCH(MAX(B33:M33),B33:M33,0)+1)

I'll watch with interest to see if anyone can solve this properly.