07-13-2019 06:23 PM
07-13-2019 06:23 PM
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.
07-13-2019 07:38 PM
You can use a HYPERLINK function which requires 2 arguments:
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)
Hope that helps
07-15-2019 01:00 PM
@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.