Home

Getting a Hyperlink to work through lookup / index

%3CLINGO-SUB%20id%3D%22lingo-sub-394089%22%20slang%3D%22en-US%22%3EGetting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394089%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20how%20is%20everyone%20doin%20today.%20New%20here%20been%20trying%20to%20figure%20out%20how%20to%20get%20this%20to%20work%20but%20I%20have%20no%20luck.%20I%20am%20attempting%20to%20utilize%20an%20automated%20lookup%20table%20to%20display%20information%20as%20long%20as%20a%20hyperlink.%20I%20have%20gotten%20it%20to%20where%20if%20you%20select%20the%20item%20you%20are%20looking%20for%20it%20will%20display%20the%20information%20you%20need%20to%20know%20about%20it%2C%20but%20when%20I%20have%20it%20look%20up%20the%20cell%20that%20is%20on%20a%20different%20sheet%20that%20has%20a%20hyperlink%20attached%20it%20will%20not%20open%20the%20link.%20I%20have%20gotten%20it%20to%20send%20you%20to%20the%20cell%20that%20the%20link%20is%20in%20on%20that%20page%2C%20but%20I%20do%20not%20want%20people%20to%20be%20able%20to%20visit%20this%20sheet%20and%20mess%20with%20it%2C%20or%20select%20the%20wrong%20link%20on%20accident.%20I%20have%20tried%20to%20use%20these%20two%20different%20strings%20to%20attempt%20this.%26nbsp%3B%3DIFERROR(HYPERLINK(%22%23%22%26amp%3BCELL(%22address%22%2CINDEX('Master%20List%20(2)'!%24C%2411%3A%24C%24152%2CMATCH(%24C13%2C'Master%20List%20(2)'!%24B%2411%3A%24B%24152%2C0)))%2C%22PDF%22)%2C%22%22)%20this%20one%20allowed%20me%20to%20go%20to%20the%20cell%20based%20off%20matching%20the%20material%20spec%20number.%20I%20tried%20going%20the%20basic%20vlookup%20method%20and%20it%20just%20gives%20me%20the%20cannot%20open%20file%20error%26nbsp%3B%3DHYPERLINK(VLOOKUP(C14%2C'Master%20List%20(2)'!%24B%2411%3A%24C%24152%2C2%2C0)).%20See%20attached%20image%20the%20highlighted%20yellow%20field%20is%20the%20selection%20list%2C%20the%20information%20below%20is%20what%20is%20pulled%20up%20based%20off%20that%20selection.%20I%20am%20trying%20to%20allow%20my%20sales%20%2F%20purchasing%20team%20to%20be%20able%20to%20open%20this%20file%20up%20by%20searching%20up%20what%20they%20need%20and%20have%20the%20hyperlinked%20pdf%20work%20off%20of%20this%20populated%20list.%20The%20original%20hyperlinked%20PDF%20is%20on%20page%20labeled%20Master%20List%20(2).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394089%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401175%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401175%22%20slang%3D%22en-US%22%3EYou%20have%20a%20hyperlink%20in%20Sheet1%20that%20jumps%20to%20another%20hyperlink%20in%20Sheet2.%20Do%20you%20want%20the%20hyperlink%20in%20Sheet2%20to%20jump%20back%20to%20the%20hyperlink%20in%20Sheet1%3F%20Please%20clarify.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401067%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20smaller%20version%20of%20the%20file%20i'm%20working%20with.%20the%20first%20page%20is%20the%20user%20interface.%20I%20want%20people%20to%20be%20able%20to%20search%20up%20the%20items%20on%20sheet%202%20and%20click%20on%20the%20hyperlink%20to%20open%20it%20on%20page%20one%20(from%20the%20link%20on%20page%202).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394739%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394739%22%20slang%3D%22en-US%22%3EYou%20need%20to%20attach%20your%20excel%20sample%20file%20so%20I%20can%20test%20how%20to%20make%20the%20hyperlink%20formula%20work%20for%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394735%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20made%20it%20work%20to%20where%20it%20will%20jump%20to%20the%20location%20of%20the%20original%20hyperlink.%20What%20i%20am%20trying%20to%20accomplish%20is%20to%20have%20the%20hyperlink%20on%20%22sheet%202%22%20while%20on%20%22sheet%201%22%20you%20have%20the%20hyperlink%20displayed%20as%20a%20lookup%2Findex%20based%20off%20of%20your%20original%20selection%20and%20opens%20the%20file%20itself%2C%20not%20directing%20you%20to%20%22sheet%202%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394110%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394110%22%20slang%3D%22en-US%22%3EPlease%20refer%20to%20my%20reply%20here%20for%20an%20idea%20of%20how%20you%20may%20construct%20your%20formula%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FLookup-data-from-sheet-link-to-that-data-on-click%2Fm-p%2F389858%23M27926%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FLookup-data-from-sheet-link-to-that-data-on-click%2Fm-p%2F389858%23M27926%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-417706%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20Hyperlink%20to%20work%20through%20lookup%20%2F%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-417706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BI%20want%20to%20be%20able%20to%20lookup%20the%20hyperlink%20on%20page%201%20from%20page%202%3CSPAN%3E%26nbsp%3Band%20have%20it%20work.%20If%20you%20use%20index%2Flookup%20command%20to%20bring%20up%20the%20cell%20on%20page%201%20where%20the%20hyperlink%20is%20from%20page%202%20i%20want%20to%20be%20able%20to%20click%20it%20and%20have%20it%20open%20up%2C%20not%20jump%20to%20page%202.%20I%20cannot%20get%20the%20hyperlink%20to%20work%20across%20sheets.%20I%20want%20to%20have%20a%20searchable%20hyperlink%20database%20on%20sheet%201%20with%20all%20of%20the%20hyperlinks%20attached%20on%20sheet%202.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
ChokolateThunder
New Contributor

Hey how is everyone doin today. New here been trying to figure out how to get this to work but I have no luck. I am attempting to utilize an automated lookup table to display information as long as a hyperlink. I have gotten it to where if you select the item you are looking for it will display the information you need to know about it, but when I have it look up the cell that is on a different sheet that has a hyperlink attached it will not open the link. I have gotten it to send you to the cell that the link is in on that page, but I do not want people to be able to visit this sheet and mess with it, or select the wrong link on accident. I have tried to use these two different strings to attempt this. =IFERROR(HYPERLINK("#"&CELL("address",INDEX('Master List (2)'!$C$11:$C$152,MATCH($C13,'Master List (2)'!$B$11:$B$152,0))),"PDF"),"") this one allowed me to go to the cell based off matching the material spec number. I tried going the basic vlookup method and it just gives me the cannot open file error =HYPERLINK(VLOOKUP(C14,'Master List (2)'!$B$11:$C$152,2,0)). See attached image the highlighted yellow field is the selection list, the information below is what is pulled up based off that selection. I am trying to allow my sales / purchasing team to be able to open this file up by searching up what they need and have the hyperlinked pdf work off of this populated list. The original hyperlinked PDF is on page labeled Master List (2). 

6 Replies

@Twifoo 

 

I have made it work to where it will jump to the location of the original hyperlink. What i am trying to accomplish is to have the hyperlink on "sheet 2" while on "sheet 1" you have the hyperlink displayed as a lookup/index based off of your original selection and opens the file itself, not directing you to "sheet 2"

You need to attach your excel sample file so I can test how to make the hyperlink formula work for you.

@Twifoo 

Attached is a smaller version of the file i'm working with. the first page is the user interface. I want people to be able to search up the items on sheet 2 and click on the hyperlink to open it on page one (from the link on page 2).

You have a hyperlink in Sheet1 that jumps to another hyperlink in Sheet2. Do you want the hyperlink in Sheet2 to jump back to the hyperlink in Sheet1? Please clarify.

@Twifoo I want to be able to lookup the hyperlink on page 1 from page 2 and have it work. If you use index/lookup command to bring up the cell on page 1 where the hyperlink is from page 2 i want to be able to click it and have it open up, not jump to page 2. I cannot get the hyperlink to work across sheets. I want to have a searchable hyperlink database on sheet 1 with all of the hyperlinks attached on sheet 2.

Related Conversations