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

dosi5127

New Member
Joined
Jun 24, 2016
Messages
2
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.

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,129,522
Messages
5,636,817
Members
416,943
Latest member
kitkat22

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
Top