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

drewzilla652

New Member
Joined
Oct 19, 2010
Messages
19
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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:

Code:
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:

Code:
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:

Code:
=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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top