Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-753741%22%20slang%3D%22en-US%22%3EHow%20to%20keep%20hyperlink%20in%20cells%20being%20accessed%20with%20IF%20function%20across%20workbooks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753741%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20that%20contains%20a%20table%20of%20insurance%20industry%20regulatory%20filings%20(called%20%22master%20filings%22).%20One%20of%20the%20columns%20provides%20a%20link%20to%20a%20PDF%20of%20the%20regulatory%20filing%20itself%2C%20for%20people%20that%20want%20to%20look%20at%20the%20original%20document.%20Can%20anyone%20help%20figure%20out%20how%20to%20carry%20those%20links%20over%20to%20a%20second%20workbook%3F%20Here's%20a%20breakdown%20of%20the%20situation%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20building%20a%20secondary%20workbooks%20that%20only%20have%20entries%20for%20regulatory%20filings%20that%20meet%20certain%20criteria.%20For%20example%2C%20I%20want%20to%20build%20a%20workbook%20that%20only%20shows%20the%20%3CSTRONG%3Elife%20insurance%3C%2FSTRONG%3Efilings%20captured%20in%20%22master%20filings%22%20and%20not%20other%20types%20of%20insurance%20filings.%20I%20successfully%20used%20an%20IF%20statement%20to%20get%20this%20data%20into%20the%20second%20worksheet%20(basically%3A%20if%20type%3Dlife_insurance%2C%20insert%20row%2C%22%22).%20The%20IF%20statement%20works%20for%20taking%20the%20actual%20text%20of%20all%20desired%20cells%20matching%20the%20life%20insurance%20reg%20filings%20into%20the%20new%20worksheet.%20However%2C%20one%20cell%20that%20contains%20a%20link%20to%20the%20online%20PDF%20of%20the%20actual%20regulatory%20filing%20does%20not%20send%20along%20the%20link.%20So%20in%20the%20master%20workbook%2C%20I%20have%20a%20working%20hyperlink%20in%20the%20word%20%22link%22%2C%20but%20when%20it%20gets%20carried%20over%20to%20the%20new%20workbook%2C%20the%20hyperlink%20isn't%20there%3B%20only%20the%20text%20for%20%22link%22%20comes%20along.%20Does%20anyone%20know%20how%20to%20get%20Excel%20to%20bring%20the%20hyperlink%20itself%20over%20to%20the%20new%20workbook%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20any%20advice!%20I%20have%20attached%20screenshots%20showing%20an%20example.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EHyperlinks%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eif%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eif%20statement%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753778%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20hyperlink%20in%20cells%20being%20accessed%20with%20IF%20function%20across%20workbooks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375961%22%20target%3D%22_blank%22%3E%40jandy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EYou%20can%20use%20a%20HYPERLINK%20function%20which%20requires%202%20arguments%3A%3C%2FP%3E%3CP%3E%3DHYPERLINK(link_location%2Cfriendly_name)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20First%20Argument%20(Link%20Location)%20should%20follow%20a%20pattern%3A%20Workbook%20Name%2C%20Worksheet%20Name%2C%26nbsp%3B%20exclamation%20mark%2C%20Cell%20Reference%3C%2FP%3E%3CP%3EYou%20can%20replace%20the%20workbook%20Name%20by%20a%20number%20sign%20if%20the%20Hyperlink%20is%20in%20the%20same%20file.%3C%2FP%3E%3CP%3EThe%20Second%20Argument%20is%20a%20user%20friendly%20Name%3C%2FP%3E%3CP%3EI%20invite%20you%20to%20watch%20my%20tutorial%20in%20which%20I%20create%20such%20function%20by%20clicking%20on%20the%20link%3A%20(at%209%3A50%20minute)%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DFeKgWSD8IWc%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DFeKgWSD8IWc%3C%2FA%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755421%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20hyperlink%20in%20cells%20being%20accessed%20with%20IF%20function%20across%20workbooks%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3Bthanks%2C%20but%20I%20don't%20need%20a%20hyperlink%20to%20take%20me%20to%20the%20cell%20in%20the%20other%20sheet.%20I%20need%20a%20hyperlink%20that%20directly%20opens%20up%20the%20PDF%20in%20OneDrive.%20The%20reason%20is%20I%20will%20share%20this%20life%20insurance-only%20sheet%20with%20some%20people%20who%20will%20not%20be%20authorized%20to%20view%20the%20master%20sheet.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
jandy
New 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

 

In this tutorial I show you how to create a list of All sheet names in any workbook by using a Macro language function (=Get.Workbook) inside a Defined name. I will then combine the Defined name with an INDEX function, a ROW function, a REPLACE function and an IFERROR function. Then we create a ...
Highlighted

@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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies