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

@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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies