+ Reply to Thread
Results 1 to 12 of 12

hyperlink as result of vlookup function not working

  1. #1
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    hyperlink as result of vlookup function not working

    Hey all,
    I have one tab called “IRM”. In this tab I have certain vendor numbers and for each number I have an explanation. Certain explanations however are hyperlinks to other excel tabs (in the same excel file, since I also work with screenshots and you can’t link a screenshot to a specific cell(?)).
    Attachment 455890
    \1

    I also have another tab “Input”. Every day I will paste new data in this tab. Each row will have a vendor number that could potentially be mentioned in the tab “IRM”+ also a unique ID number.
    Attachment 455889
    \1

    I would like to create an excel file where I paste the info in “Input” and where I get a list of the ID numbers of which the vendor number is mentioned in the tab “IRM” + the related explanation which could be a hyperlink to a different tab.

    What I tried:
    through Vlookup function I could create a new table of which I create a pivot table which I can then easily refresh by pressing “refresh all” after I entered new data in “Input”.
    The problem that I keep getting however is that if the result of the Vlookup is a cell with a hyperlink, the hyperlink does not work. Tried to mess around with =Hyperlink(Vlookup) but it also doesn’t seem to work.
    =HYPERLINK(VLOOKUP(B4;IRM!A:B;2;0)) … with B4 mentioning the vendor number in my new table. It gives me the message “cannot open the specified file”

    Anyone has any ideas? Maybe I’m approaching this wrong?

    Thanks in advance for the assistance,
    Bart,

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: hyperlink as result of vlookup function not working

    Use INDEX / MATCH instead VLOOKUP()
    or, if you love VLOOKUP() read here or here
    Last edited by sandy666; 04-13-2016 at 11:59 AM. Reason: link added

  3. #3
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    Re: hyperlink as result of vlookup function not working

    Hey Sandy,

    Thank you for the assistance however I’m having the same result as with the Vlookup. The hyperlink is lost when it is a result of formula’s.

    So I used both of the following combinations:
    1) =INDEX(IRM!A:B;(MATCH(calculation!B6;IRM!A:A;0));2)
    2) =HYPERLINK(INDEX(IRM!A:B;(MATCH(calculation!B6;IRM!A:A;0));2))

    Situation:
    Tab IRM => has all the explanations per vendor number. I created an hyperlink for vendor 2242000590 to the tab “explanationtest3”.
    http://i68.tinypic.com/2lnzlg6.png


    Tab Input is where I paste all new data.

    Tab Calculation is where I create a new table based on the information in tab Input.
    http://i63.tinypic.com/2ewhac0.png

    In the column I have now put the formula of (hyperlink/)index/match. For vendor 22242000590 I did not use hyperlink function, for vendor I224DK2001 I did but when I click on the link it gives me the following message:
    http://i63.tinypic.com/ruzded.png

    Maybe I should use a different approach? However I’m not sure where to start looking.
    What I’d like to have is when I paste information in the excel file, that it will provide me a list of the ID numbers that have vendor numbers which are written in the IRM tab thus providing me the related explanation.

    The reason I have to use hyperlinks is that there are also screenshots involved. They are making things complicated since you can’t have a screenshot as a result of a vlookup function/ index function.

    Again thank you for the effort,

  4. #4
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    Re: hyperlink as result of vlookup function not working

    Hey Sandy,

    Thank you for the assistance however I’m having the same result as with the Vlookup. The hyperlink is lost when it is a result of formula’s.

    So I used both of the following combinations:
    1) =INDEX(IRM!A:B;(MATCH(calculation!B6;IRM!A:A;0));2)
    2) =HYPERLINK(INDEX(IRM!A:B;(MATCH(calculation!B6;IRM!A:A;0));2))

    Situation:
    Tab IRM => has all the explanations per vendor number. I created an hyperlink for vendor 2242000590 to the tab “explanationtest3”.
    http://i68.tinypic.com/2lnzlg6.png


    Tab Input is where I paste all new data.

    Tab Calculation is where I create a new table based on the information in tab Input.
    http://i63.tinypic.com/2ewhac0.png

    In the column explanation I have now put the formula of (hyperlink/)index/match. For vendor 22242000590 I did not use hyperlink function, for vendor I224DK2001 I did but when I click on the link it gives me the following message:
    http://i63.tinypic.com/ruzded.png

    Maybe I should use a different approach? However I’m not sure where to start looking.
    What I’d like to have is when I paste information in the excel file, that it will provide me a list of the ID numbers that have vendor numbers which are written in the IRM tab thus providing me the related explanation.

    The reason I have to use hyperlinks is that there are also screenshots involved. They are making things complicated since you can’t have a screenshot as a result of a vlookup function/ index function.

    Again thank you for the effort,

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: hyperlink as result of vlookup function not working

    I don't see any pictures so if you want to continue attach example file.xlsx.
    regards

  6. #6
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    Re: hyperlink as result of vlookup function not working

    I'm having difficulties attaching the excel file on this forum (this is why I tried using images)

    I went to manage attachments: uploaded the excel file there. If I go to my account, look at the attachments I can see it there but it states "in progress"...

    When I reply (advanced) and I click on the "attachments" function, it wants to show me a dropdown list but it is empty.

    I suppose you didn't click on the image links to avoid viruses or something so not sure if it will help if I upload the file on some other website?

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: hyperlink as result of vlookup function not working

    Type any text

    Go Advanced
    Manage attachments
    Browse (for excel file)
    Upload (ecel file what will you see on left side under Browse)
    Close
    Save/Send or something

  8. #8
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    Re: hyperlink as result of vlookup function not working

    So I typed this message, went to manage attachment, uploaded the excel file so it should now be included in this message?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    Re: hyperlink as result of vlookup function not working

    there you go ^^

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: hyperlink as result of vlookup function not working

    In D6 on Calculation sheet: =HYPERLINK("#INDEX(IRM!A:B,(MATCH(calculation!B6,IRM!A:A,0)),2)",INDEX(IRM!A:B,(MATCH(calculation!B6,IRM!A:A,0)),2))

    Edit.
    btw, in post #2 here you've second link where (after jump there) in post #4 - a little explanation how to do it and example xlsx file also.

    result of above formula is: explanationtest5!A1 so if you want jump to this address (explanationtest5!A1) simply add "#"& in front of index:
    =HYPERLINK("#"&INDEX(IRM!$A$2:$B$7,MATCH(calculation!B6,IRM!$A$2:$A$7,0),2))
    but if the source will not be an address (e.g. blah blah blah) you will get error. That means result of formula (INDEX..etc.) should return address.

    general:
    you need decide what formula return: text or address
    If formula return text, use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If formula return address, use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in these cases you can drag down both, but you cannot use one kind of formula with mixed results. (Sometimes you can, but it depends where you want to jump with hyperlink)
    Last edited by sandy666; 04-15-2016 at 10:47 AM. Reason: formula changed

  11. #11
    Registered User
    Join Date
    04-13-2016
    Location
    Belgium, Antwerp
    MS-Off Ver
    365 (version 2301)
    Posts
    18

    Re: hyperlink as result of vlookup function not working

    Thank you so much!!!

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: hyperlink as result of vlookup function not working

    You are welcome

    If anyone has helped you, then thank them by clicking on their reputation star * bottom lef then mark thread SOLVED! (Above Your first post --> Thread Tools --> Mark thread as Solved)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hyperlink not working from Vlookup
    By Jamber89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2015, 06:25 PM
  2. [SOLVED] Conditional format a cell result from Vlookup not working
    By thecdnmole in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2014, 10:55 AM
  3. Dynamic Hyperlink function not working
    By mongoose36 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 09:26 AM
  4. Replies: 5
    Last Post: 06-10-2013, 07:01 AM
  5. [SOLVED] Use VLookup result at hyperlink
    By keredu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2013, 01:55 PM
  6. VLookup result not clickable hyperlink
    By CHoedeman in forum Excel General
    Replies: 5
    Last Post: 02-16-2011, 08:54 AM
  7. [SOLVED] Hyperlink Function not working as expected
    By Hari Prasadh in forum Excel General
    Replies: 2
    Last Post: 04-16-2005, 09:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1