Find lowest value in range with link

[pujo]

Good day peeps!
I have a simple formula in cell K2 that will find the lowest value in the range.

=MIN(IF(ISNUMBER(K7:K8647),K7:K8647,""))

There is a lot of rows to scroll down to get to this cell (K5460) and view the inline data.

Is there any way possible to make cell K2 a link to the lowest value cell in the range, dynamically?
Data is different from day to day.

Cheers!

 

[Automatrix]

How about a helper column:

=IF(K7=$K$2,"Lowest value","")

Then you can filter this column for "Lowest value".

 

[Peter_SSs]

Try this formula in K2

=HYPERLINK(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(CELL("filename",K2),"\",REPT(" ",100)),100)),"]","]'")&"'!K"&6+MATCH(MIN(K7:K8647),K7:K8647,0),MIN(K7:K8647))

 

[pujo]

Thanks gentlemen, much appreciated.

I attempted to do something like this however, the solution Peter posted worked perfectly and no need for vba.

K2 =ADDRESS(MIN(IF(ISNUMBER(K7:K8647),K7:K8647,"")),8)

Since my calculations are set to manual, in the calculation sub-routine I had this.

With Sheets("Sheet1")
    .Range("K3").Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets("Sheet1").Range("K2").Value
End With

 

[Peter_SSs]

You're welcome. Glad to help.

 

[pujo]

You're welcome. Glad to help.

@Peter_SSs
This morning after polling the new data, the formula no longer works.
The range has increased from K7:K8647 to K7:K1048576 and have changed the formula to reflect the range.
After changing, the formula, the result is #N/A

Is it possible to add the argument IF ISNUMBER as well as capture the entire column?

ISNUMBER to count for the polling interval where there is "NO DATA", and the entire column as the range is dynamic in size.

Cheers!

 

[Peter_SSs]

• I would definitely try to avoid using whole column references. Surely you don't have anything like a million rows?
• Please post the actual formula that you now have.
• What does your original formula return with the new data and that massive range?
  

  =MIN(IF(ISNUMBER(K7:K1048576),K7:K1048576,""))

• ISNUMBER to count for the polling interval where there is "NO DATA", and the entire column as the range is dynamic in size.

  I don't know what that means
  

 

[pujo]

• I would definitely try to avoid using whole column references. Surely you don't have anything like a million rows?
• Please post the actual formula that you now have.
• What does your original formula return with the new data and that massive range?
  

  =MIN(IF(ISNUMBER(K7:K1048576),K7:K1048576,""))

• 
  I don't know what that means

I am going to put together some test data and use BBCode top post it.