Closed Thread
Results 1 to 23 of 23

Hyperlink to a cell in another worksheet using VLookup

  1. #1
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Hyperlink to a cell in another worksheet using VLookup

    Hi

    I hope someone can help me out on this as I've been searching on the internet for a solution for a long time without success.

    I know someone has probably asked similar question before but the search has brought up too many to go through.

    I'm looking to link from a cell in one worksheet to another based on the matching value using the Hyperlink function. I am aware this can be done manualyy through the Insert Hyperlink on the Excel Menu but this would be to painful when I have hundreds on items to link. I want a dynamic formula using with a combination of VLookup or Match where I would be able to drag the formula across.

    I have attached the Excel file as an example of what I'm trying to achieve. Your help is much appreciated.

    Regards

    e_lad
    Attached Files Attached Files
    Last edited by e_lad; 03-04-2010 at 10:42 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    Not entirely clear to me I'm afraid ... perhaps:

    Sheet1!A2:
    =HYPERLINK("#INDEX(sheet2!A:A,MATCH(100,Sheet2!A:A,0))",100)

    but of course in the above you're hard wiring the values to search for... would be be better IMO to store the link in adjacent columns such that the values to be searched for become dynamic, eg:

    Sheet1!C2:
    =HYPERLINK("#INDEX(Sheet2!A:A,MATCH("&A2&",Sheet2!A:A,0))","View "&A2)
    copied down

  3. #3
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    DonkeyOne, thanks for your quick response.

    What if I want to link to the value on the Amount column in Sheet 2 based on the relevant invoice number?

    Can the same result also be achieved through a combination of Hyperlink and VLookup (as VLookup where you do not have to quote a specific column in the formula for search) functions?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    I'm afraid I don't follow - are you saying you want Sheet2!B2 to also be a link linking back to Sheet1!A2 (based on the value in Sheet2!A2) ?

  5. #5
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Hyperlink to a cell in another worksheet using VLookup

    I'm not sure if I'm understanding you correctly, but this may help. The attached file makes a link in column B based on the value in column A (in Sheet1). It will link to the same value on Sheet2. Because it looks at the filename in the forumla, you need to save it on your comp and open it from there.

    This is the formula, though there may be an easier way to do it:
    =IF(A1="","",HYPERLINK("["&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)&"]'Sheet2'!A"&MATCH(A1,Sheet2!$A$1:$A$295,0),"Link"))

    If the range you were searching in on Sheet2 started at A2 then you would have to add 1 (I've underlined it below)
    =IF(A1="","",HYPERLINK("["&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)&"]'Sheet2'!A"&MATCH(A1,Sheet2!$A$1:$A$295,0)+1,"Link"))

    If it started on A10 you would have to add 9 etc etc.

    Edit: I added a link that will take you to a place based on the value of the cell using the hyperlink function. You would have to edit the number value in 2 places in the formula in order for it to work. This probably isn't what you want but it may help in some way.
    Attached Files Attached Files
    Last edited by The Phil; 03-02-2010 at 03:25 PM.

  6. #6
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    Quote Originally Posted by DonkeyOne View Post
    I'm afraid I don't follow - are you saying you want Sheet2!B2 to also be a link linking back to Sheet1!A2 (based on the value in Sheet2!A2) ?
    The answer to your question is Yes and also by clicking on Sheet 1 Cell A2 for eg to link to Sheet 2 on the corresponding cell in column B
    Last edited by e_lad; 03-02-2010 at 04:08 PM. Reason: Posting in error

  7. #7
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    Hi Phil, what if I wanted the outcome of the link to be directed at the corresponding cell in Column B instead of A?

  8. #8
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Hyperlink to a cell in another worksheet using VLookup

    Quote Originally Posted by e_lad View Post
    Hi Phil, what if I wanted the outcome of the link to be directed at the corresponding cell in Column B instead of A?
    If you want to change the cell that it looks at for the value, you need to change the two values underlined below:
    =IF(A1="","",HYPERLINK("["&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)&"]'Sheet2'!A"&MATCH(A1,Sheet2!$A$1:$A$295,0),"Link"))

    If you want to change the column that it points to you need to change the 2 values underlined below (notice the "A" all by itself that needs to be changed as well):
    =IF(A1="","",HYPERLINK("["&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)&"]'Sheet2'!A"&MATCH(A1,Sheet2!$A$1:$A$295,0),"Link"))


    I'm not entirely sure what you are asking so I hope this answers it. If it doesn't try to be very specific.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    As I see it there is zero need for Volatile CELL in this instance - this adds unnecessary complexity and calculation overheads to proceedings.

    Sheet1!A2: - as before but with adjust B range.
    =HYPERLINK("#INDEX(sheet2!B:B,MATCH(100,Sheet2!A:A,0))",100)

    Sheet2!B2:
    =HYPERLINK("#INDEX(Sheet1!A:A,MATCH("&A2&",Sheet1!A:A,0))",50)

    but in these instances as before you're hardwiring the values within the functions which is generally ill advised. You would be better using alternative cells to store the hyperlinks.

    If you want to automate the hyperlinks within the cells and without using the hardwired functions I'm afraid you're looking at VBA - and assuming the values in the cells change that in itself will not be entirely trivial (will necessitate event driven code).

  10. #10
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    Thanks again DonkeyOte. That works perfectly!

    I generally understand how INDEX and MATCH together work in the basic form but with the formula
    =HYPERLINK("#INDEX(Sheet1!A:A,MATCH("&A2&",Sheet1!A:A,0))",50) provided above, for my own learning sake, how can you have the "& sign without anything proceeding it? I never knew this would work.

  11. #11
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    Quote Originally Posted by The Phil View Post
    If you want to change......I'm not entirely sure what you are asking so I hope this answers it. If it doesn't try to be very specific.
    Phil, appreciate all your help on this. It's shed a different light how this can be achieved.

  12. #12
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Hyperlink to a cell in another worksheet using VLookup

    Quote Originally Posted by DonkeyOte View Post
    As I see it there is zero need for Volatile CELL in this instance - this adds unnecessary complexity and calculation overheads to proceedings.

    Sheet1!A2: - as before but with adjust B range.
    =HYPERLINK("#INDEX(sheet2!B:B,MATCH(100,Sheet2!A:A,0))",100)

    Sheet2!B2:
    =HYPERLINK("#INDEX(Sheet1!A:A,MATCH("&A2&",Sheet1!A:A,0))",50)

    but in these instances as before you're hardwiring the values within the functions which is generally ill advised. You would be better using alternative cells to store the hyperlinks.

    If you want to automate the hyperlinks within the cells and without using the hardwired functions I'm afraid you're looking at VBA - and assuming the values in the cells change that in itself will not be entirely trivial (will necessitate event driven code).
    LOL I knew there would be an easier way than my rediculous formula.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    Quote Originally Posted by e_lad View Post
    I generally understand how INDEX and MATCH together work in the basic form but with the formula
    =HYPERLINK("#INDEX(Sheet1!A:A,MATCH("&A2&",Sheet1!A:A,0))",50) provided above, for my own learning sake, how can you have the "& sign without anything proceeding it? I never knew this would work.
    In essence the link you're creating is a text string denoted by use of encasing " marks (just that in this case the text string is in essence a formula).
    Within the formula the references need to adapt based on content of a given cell - we use & as alternative to concatenate, eg:

    A1: Apples
    B1: Pears

    C1: =A1&" and "&B1

    is the same as

    D1: =CONCATENATE(A1," and ",B1)

    both will return "Apples and Pears"

  14. #14
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    DonkeyOte

    Why isn't the formula
    =HYPERLINK("#INDEX(Sheet1!A:A,MATCH("&A2&",Sheet1!A:A,0))",50)
    ,working when I replace the highlighted parts of the formula with a dynamic worksheet name formula of this
    =MID(CELL("filename",'Sheet2'!A:A),FIND("]",CELL("filename",'Sheet2'!A:A))+1,256)&"!"&"A:A" ?

    I would need to be able to allow for circumstances where the sheet name maybe subsequently changed. This would affect all the links using the formula provided.

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    Rather than introducing Volatility by virtue of the CELL function I would suggest you simply create Defined Name(s) that refer to the appropriate columns ,eg:

    Name: _rngS1
    RefersTo: =Sheet1!$A:$A

    Simply then substitute Sheet1!A:A in existing function with _rngS1

    =HYPERLINK("#INDEX(_rngS1,MATCH("&A2&",_rngS1,0))",50)

    Renaming the sheet will not affect the hyperlinks given the named range is constant and that will adjust per the rename.

  16. #16
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    That seems to have done the trick. I appreciate your advice to avoid volatile functions if possible but for curitosity why wouldn't the CELL function I quoted work? Is it because it's been intepreted as cell address or text? I'm confused.

    To complicate matters, I'm also trying to link the same based upon multiple criteria. I've attached another file (actual document in use) whereby I would like to link from "Inv" sheet (Sheet 1) to "Inv Breakdown" sheet (Sheet 2) pointing at the corresponding cell in Column D ("Invoiced") when the links in Sheet 1 Column I are clicked. This time the link is to be based on the following 2 (perhaps more) matching criteria, i.e.:

    ■ Invoice No. as well as
    ■ PO No

    Sorry to be a pain...can this be achieved at all?
    Attached Files Attached Files

  17. #17
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    Simplicity is always best so I would suggest you do the following:

    'Inv Breakdown'!L4
    =A4&"@"&B4
    copied down for all rows

    Create new named ranges

    Name: Key_Column
    RefersTo: 'Inv Breakdown'!$L:$L

    Name: Inv_Column
    RefersTo: 'Inv Breakdown'!$D:$D

    Then

    Inv!I4:
    =IF(OR(A4="",H4=""),"",HYPERLINK("#INDEX(Inv_Column,MATCH("""&H4&"@"&A4&""",Key_Column,0))","►"))
    copied down

    Note for I4 you will get an invalid link given the combination of PO & Inv. No does not exist.

  18. #18
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Hyperlink to a cell in another worksheet using VLookup

    DO....nice work!!! You're a star!

    One question though, why do we need the extra quotation marks (highlighted red below) compared to the last formula?

    =IF(OR(A4="",H4=""),"",HYPERLINK("#INDEX(Inv_Column,MATCH("""&H4&"@"&A4&""",Key_Column,0))","►"))

    Does this have anything to do with the number of matching criteria? The reason for the question is so that I know what to adjust in the formula should I decide to include more criteria the same way.

  19. #19
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Hyperlink to a cell in another worksheet using VLookup

    Perhaps easiest to explain using a more basic example

    A1: ="Apple" --> Apple

    A2: ="""Apple""" --> "Apple"

    ie the initial quotation marks denote a text string - they aren't meant to represent a literal character - in the 2nd example they are generated literally.

    We need the latter in our function - we need to encase our criteria string within quotation marks in order for the MATCH to work correctly, ie:

    MATCH(Apples@Pears,range,0)

    will generate an error... we need

    MATCH("Apples@Pears",range,0)

    so in our HYPERLINK string we use """ to generate the quotations as literals in the resulting string.

    (note: for @ we do not create the quotations literally as we do not want to generate "@" we want to generate @)

  20. #20
    Registered User
    Join Date
    04-14-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Hyperlink to a cell in another worksheet using VLookup

    Hi there,

    I'm digging up an old post, but I found a solution here to a problem I have been trying to solve for some time!

    Using parts of the fomula within this thread I have half sovled my problem.

    I use 2 worksheets for a list of customers and the second for a sales report for said customers.

    I wanted to create a link from the sales report to go deirctly to the row for that customer on the 'call log' workbook by using their unique customer ID

    On the 'Sales Report' workbook I have 2 columns that are important to this problem; The first is a hyperlink using a derivative of the formula discussed in this post. The second column is a customer ID.

    The formula is as follows

    =HYPERLINK("#INDEX('[Call_Log.xlsx]My Patch'!A:A,MATCH(B2,'[Call_Log.xlsx]My Patch'!A:A,0))",B2)

    So the forumal looks at the Call log and matches up any occurance of B2 within column A. Note two instances of B2 in the formula, the first is the reference point, the second is the value of the field for demonstration purposes only (this could say 'XYZ')

    Now, this actually works really well other than one slight problem as it will link to the correct customer on the second document. The issue lies when I drag the formula down to populate the entire workbook. The last instance of B2 will increment relatively, so what I actually have is two instances of the cuystomer ID side by side,. one is the reference point, one is the hyperlink. Unfortunately however the first instance of B2 will not increment?

    =HYPERLINK("#INDEX('[Call_Log.xlsx]My Patch'!A:A,MATCH(B2,'[Call_Log.xlsx]My Patch'!A:A,0))",B2)

    If this were the opposite we could use $ to force it to stay the same for either the letter or number reference. Is there a similar case to force it to increment?


    Many thanks for any help / advice.


    Kind regards,
    M

  21. #21
    Registered User
    Join Date
    04-14-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Hyperlink to a cell in another worksheet using VLookup

    I have also tried removing the match statement and calling on it from another field to see if I could get the reference to increment, it wouldn't...

  22. #22
    Registered User
    Join Date
    04-14-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Hyperlink to a cell in another worksheet using VLookup

    Bump...

  23. #23
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Hyperlink to a cell in another worksheet using VLookup

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

Closed Thread

Thread Information

Users Browsing this Thread

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

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