Confirmation To See If I'm Corrrect...

[Bubbis Thedog]

On Worksheet1, I have rows 6 through 41, column E (E6:E41), filled with
numbers. On that same worksheet, column A (A, B, and C merged [This IS
considered column A, right?]) indicates the name of the person
correllating to those numbers.

A B C D E
6 Harold 7
7 Jane 3
8 Ron 1
. .
. .
. .
40 Dave 4
41 Tom 5

Now, there's a worksheet2 where I have a cell listing the maximum value
from worksheet1 [The cell formula I have is =MAX(Worksheet1!E6:E41)],
and to the left of that cell I want the name of the person correllating
to that maximum value to be listed also:

Harold 7

Would this be the correct formula to use?

=CELL("contents",OFFSET(INDIRECT("E"&MATCH(MAX(Worksheet1!E6:E41),Worksh
eet1!E6:E41,0)),0,-4,1,1))

(BTW... Thanks, tsides, for getting me started the other day on this.
This post states exactly what I was wanting to do.)

Thanks in advance to anyone who can help me on this. The formula makes
sense to me, but I can't get it to work for some reason.

(BTW 2... INDIRECT is the correct function to use in this scenario,
right?)

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[JE McGimpsey]

If it works, it's "correct". This might be a bit easier, though:

Assuming the cell with your MAX() formula is B1:

A1: =INDEX(Sheet1!A6:A41,MATCH(B1,Sheet1!E6:E41, FALSE))

 

[Bubbis Thedog]

Yep, that worked perfectly. I was not too familiar with the INDEX
function, but now I am. Thanks so much for your assistance and have a
great night.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***