can I reference a cell as someting like: cell(1, 1) ?

[dukof]

What I want to do is to link to a value in column A, with the row specified by the cell of highest value in column "D".

I want to do this by a single cell function. I'm thinking someting like

=Cell(1, row(Address(High(D:D))))

Obviously, this is just my thought up function... But is this possible to do, and how would I code it?

[Barb Reinhardt]

Try using the match function to get the row number you need:

=MATCH(MAX(D:D),D:D,0)

Be aware that match gives the relative position in the array.

I'm not sure what you want to do with it however. I'm thinking that the
INDIRECT function might get you where you want to go.

If this isn't what you want, come back and clarify.

"dukof" <[email protected]> wrote in message
news:[email protected]...
>
> What I want to do is to link to a value in column A, with the row
> specified by the cell of highest value in column "D".
>
> I want to do this by a single cell function. I'm thinking someting like
>
>
> =Cell(1, row(Address(High(D:D))))
>
> Obviously, this is just my thought up function... But is this possible
> to do, and how would I code it?
>
>
> --
> dukof
> ------------------------------------------------------------------------
> dukof's Profile:
> http://www.excelforum.com/member.php...o&userid=29084
> View this thread: http://www.excelforum.com/showthread...hreadid=488127
>

[dukof]

Yes, Thanks!

That's was a good start.

Actually, I now found the offset function, which combined with this will give what I was looking for:

=OFFSET(A1; MATCH(MAX(D:D);D:D; 0) - 1;0)

This will give me the value at column A, at the row specified with the max value of column D.


If anyone know some alternative ways to code this function I would be interested to know about them too.

Thanks a lot!