Getting a Cell Address in Excel

[westma]

I have a column of data and all I want is the address of the cell that contains the maximum value in that column. How can I do this?

 

[MarkBeck]

Try the following

I assume that your column of data is "A", otherwise replace the A:A and use the numerical equivelant of the column in the last digit of the formula (herein 1).

=ADDRESS(MATCH(MAX(A:A),A:A,0),1)

Good luck


Mark

 

[KenWright]

One way, with your range of data named Range, the following formula should do it:-

=ADDRESS(MATCH(MAX(Range),Range,0)+ROW(Range)-1,COLUMN(Range))

Regards
Ken..........

 

[westma]

Thank you so much, this was just what I was missing.

Rgds,
MW

 

[Queryman]

If you know the address of that max cell, can you find the value in that address?





Michael

 

[SkipVought]

Queryman,

Use the INDIRECT worksheet function with the address as the argument.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[davidmunoz]

to add to the reply below, if you wanted to eliminate the absolute "$" sign in the formula,you would have to add this (just change your column ref accordingly as stated before):

for info found in A column-

=REPLACE(REPLACE(ADDRESS(MATCH(MAX(A:A),A:A,0),1),1,1,"&quot,FIND("$",REPLACE(ADDRESS(MATCH(MAX(A:A),A:A,0),1),1,1,"&quot),1,"&quot

for info found in B column-

=REPLACE(REPLACE(ADDRESS(MATCH(MAX(B:B),B:B,0),2),1,1,"&quot,FIND("$",REPLACE(ADDRESS(MATCH(MAX(B:B),B:B,0),2),1,1,"&quot),1,"&quot

Hope this helps and not confuses!

 

[Queryman]

If I wanted to get the max values address of the cell from the same column on every non-blank page in the worksheet, how would you modify this? The address would be diffrent for each page.





Michael