Converting a Cell Value to Cell Reference...

[Bubbis Thedog]

Here's the deal: I've set up a MAX formula that displays resultant
value, and I need to figure out how to "tell" a particular function what
cell that value is located in. I am working with the OFFSET function,
and I need a cell reference as the first parameter, and all that I can
provide right now is the value for a MAX from a chosen range. Any
advice is greatly appreciated.

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

 

[Bubbis Thedog]

I suppose what I'm asking is... Is there a function that converts a
singular value to a cell reference?

I'll try to illustrate...

=OFFSET({Function Name}(MAX(Thomas!E5:E4)),2,2,2)

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

 

[tsides]

=OFFSET({Function Name}(MAX(Thomas!E5:E4)),2,2,2)


In your example, the syntax of E5:E4 doesn't look right to me - when I
do that, Excel automatically changes it to E4:E5. Anyway, assuming
your looking at a single column of numbers, try the following:

=OFFSET(INDIRECT("E"&MATCH(MAX(E1:E4),E1:E4,0)),2,2,2)

In a column of:
6
8
2
4

MAX(E1:E4) returns the 8
MATCH(8,E1:E4,0) returns the index number of the first 8, which in
this case matches it's row number, which is 2
INDIRECT("E2") converts the text representation of the address into
the address for use in your offset statement.

Remember, if your data range is actually E4:E7, all you have to do is
add 4 to the index returned by the MATCH:
=OFFSET(INDIRECT("E"&4+MATCH(MAX(E1:E4),E1:E4,0)),2,2,2)

Alternative solution:
Add a column F (hide it if you need to) which stores the addresses of
column E. In column F, enter the formula and copy it down:
="R"&ROW(E4)&"C"&COLUMN(E4)

Then, you can use the following, without eevr having to worry about
adding the 4 your data starts on row 4 instead of row 1:
=OFFSET(INDIRECT(VLOOKUP(MAX(E4:E7),E4:F7,2),FALSE),2,2,2)