Using INDEX function, would like to create hyperlink that also works when saved in PDF

[dosi5127]

Hello!

I am using Excel version 1808 on a Windows 10 computer.

I have the document set up so if, for example, a user types "John" in cell A1, it pulls the phone number for "John" into cell A2. I have a list of names and numbers on a separate tab that is being referenced, and I'm using the INDEX and MATCH functions to complete this task.

=INDEX(Reference!B1:B20,MATCH('Cover Sheet'!A1,Reference!A1:A20,0),0))

Ultimately, this document becomes a PDF, and this is reused multiple times a day. What I am trying to achieve is that the phone number that the document pulls into cell A2 be hyperlinked, in a way that when I print to PDF (or save as PDF), the link still works. That way, if I view the file on my phone, I can just click the number to make a quick call.

I know I can manually hyperlink the phone number each time by adding "tel:" in the hyperlink area, but I am hoping for a more streamlined approach. I tried using the HYPERLINK function along with CONCATENATE to make it so when the phone number comes in, it shows up as "tel:555-555-5555" in the cell and the hyperlink works while in excel, but it doesn't work once I print to PDF.

EXAMPLE:

=HYPERLINK(CONCATENATE("tel:",(INDEX(Reference!B1:B20,MATCH('Cover Sheet'!A1,Reference!A1:A20,0),0))))

I have also tried putting the hyperlink on the phone number in the list I'm pulling from, to see if it would pull in that way (but I should have known better, ha) and that didn't work.

I've tried searching this on Google every which way, and am stumped. Any help is much appreciated.

 

[John_w]

It seems that links created by the HYPERLINK function are not clickable in the PDF when the sheet/workbook is saved as a PDF. However, links created via Insert -> Link, or the right-click cell context menu, are clickable in the PDF. Therefore one solution is to convert the HYPERLINK function result to a Link. This macro converts all HYPERLINK function results on the active sheet to the equivalent Link in the same cell:

Public Sub Change_Hyperlink_Formulas_To_Links()

    Dim cell As Range
    Dim cellValue As Variant
    
    For Each cell In ActiveSheet.UsedRange
        If InStr(1, cell.Formula, "=HYPERLINK(", vbTextCompare) = 1 Then
            cellValue = cell.Value
            cell.Formula = ""
            cell.Hyperlinks.Add Anchor:=cell, Address:=cellValue
        End If
    Next
    
End Sub