How to keep hyperlink in cells being accessed with IF function across workbooks?

Copper Contributor

Hello community,

 

I have a workbook that contains a table of insurance industry regulatory filings (called "master filings"). One of the columns provides a link to a PDF of the regulatory filing itself, for people that want to look at the original document. Can anyone help figure out how to carry those links over to a second workbook? Here's a breakdown of the situation:

 

I am building a secondary workbooks that only have entries for regulatory filings that meet certain criteria. For example, I want to build a workbook that only shows the life insurance filings captured in "master filings" and not other types of insurance filings. I successfully used an IF statement to get this data into the second worksheet (basically: if type=life_insurance, insert row,""). The IF statement works for taking the actual text of all desired cells matching the life insurance reg filings into the new worksheet. However, one cell that contains a link to the online PDF of the actual regulatory filing does not send along the link. So in the master workbook, I have a working hyperlink in the word "link", but when it gets carried over to the new workbook, the hyperlink isn't there; only the text for "link" comes along. Does anyone know how to get Excel to bring the hyperlink itself over to the new workbook? 

 

Many thanks for any advice! I have attached screenshots showing an example. 

2 Replies

@jandy 

Hi

You can use a HYPERLINK function which requires 2 arguments:

=HYPERLINK(link_location,friendly_name)

 

The First Argument (Link Location) should follow a pattern: Workbook Name, Worksheet Name,  exclamation mark, Cell Reference

You can replace the workbook Name by a number sign if the Hyperlink is in the same file.

The Second Argument is a user friendly Name

I invite you to watch my tutorial in which I create such function by clicking on the link: (at 9:50 minute)

https://www.youtube.com/watch?v=FeKgWSD8IWc

Hope that helps

Nabil Mourad

 

@nabilmourad thanks, but I don't need a hyperlink to take me to the cell in the other sheet. I need a hyperlink that directly opens up the PDF in OneDrive. The reason is I will share this life insurance-only sheet with some people who will not be authorized to view the master sheet.