Indirect function to a cell with a hyperlink; follow the hyperlink

[drewzilla652]

I have a series of hyperlinks in cells that display dates. I am looking for a formula that will reside in a different cell, that determines the latest (max) of these dates, and then will have a hyperlink that connects to the hyperlink of that cell.
That is, I would like to have hyperlink in a cell, and have it open the hyperlink of a different cell. The selection of that different cell is based on a determination as to whether the date displayed in that cell is the maximum value of a range of cells (each cell has different hyperlinks).
Thank you! in advance

 

[DonkeyOte]

By the sounds of it that won't be possible with native functions.

When you retrieve the content of a cell with a hyperlink in effect you pull only the visible text of the hyperlink cell, eg:

A1: 
=HYPERLINK("http://www.google.com",TODAY())

B1: 
=A1

B1 will only return today's date as a constant - not the underlying address.

Given the above I suspect you would need to use a User Defined Function, eg:

Function GetLink(rngDates As Range) As String
    Dim dtMax As Date, rngCell As Range
    dtMax = Application.Max(rngDates)
    For Each rngCell In rngDates.Cells
        If CLng(rngCell) = CLng(dtMax) Then
            GetLink = rngCell.Hyperlinks(1).SubAddress
            Exit For
        End If
    Next rngCell
End Function

Then something like:

=HYPERLINK("#"&GETLINK((B4,B10,B17,B23)),TEXT(MAX(B4,B10,B17,B23),"dd-mm-yyyy"))

in the above we assume internal links (#) - adapt as nec.

In principle the UDF finds the Max of the passed cells and pulls the underlying address such that you can generate the link
(the subsequent MAX generates the text to display element)