+ Reply to Thread
Results 1 to 12 of 12

Link cells in adjoining sheets if contents match

  1. #1
    Registered User
    Join Date
    03-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Link cells in adjoining sheets if contents match

    I have a list of items in column A of Sheet1 and the same list in column A of Sheet2. Both list will contain the same items, however not necessarily in the same order.

    What I want the book to do is; when I click on an item in the list on Sheet1, it takes me to the same item in Sheet2.

    Is this possible with VBA?
    Last edited by Militia; 04-22-2009 at 06:59 AM.

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

    Re: Link cells in adjoining sheets if contents match

    [removed - error]
    Last edited by DonkeyOte; 04-21-2009 at 10:55 AM.

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

    Re: Link cells in adjoining sheets if contents match

    Apologies earlier... this is one of NBVC's methods:

    Sheet1!B1: =HYPERLINK("#"&CELL("address",INDEX(Sheet2!A:A,MATCH(A1,Sheet2!A:A,0))),A1)

    Where A1 holds current value to be found on Sheet 2
    Last edited by DonkeyOte; 04-21-2009 at 11:05 AM. Reason: Friendly needed to be updated

  4. #4
    Registered User
    Join Date
    03-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Link cells in adjoining sheets if contents match

    Many thanks DonkeyOte, though I can't get it to work.

    My list on Sheet1 starts on row 8. What part of the formula should I change?

    Do I need to substitute anything else to make it work for me?

    Cheers
    Militia

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

    Re: Link cells in adjoining sheets if contents match

    Quote Originally Posted by Militia
    My list on Sheet1 starts on row 8. What part of the formula should I change?
    In which case try:


    Sheet1!B8: =HYPERLINK("#"&CELL("address",INDEX(Sheet2!A:A,MATCH(A8,Sheet2!A:A,0))),A8)

  6. #6
    Registered User
    Join Date
    03-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Link cells in adjoining sheets if contents match

    By doing that i get a pop-up:

    "Cannot open the specified file"

    I attach my workbook so you can see the error. I've only hyperlinked the first in the list.
    Attached Files Attached Files

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

    Re: Link cells in adjoining sheets if contents match

    Maybe we're talking at x-purposes... see attached:
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Link cells in adjoining sheets if contents match

    Perfect DonkeyOte,

    Again, many thanks.

  9. #9
    Registered User
    Join Date
    03-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Link cells in adjoining sheets if contents match

    I don't know if I should start a new thread or not. But I wonder if this is also possible;

    In a column on Sheet2 of the same workbook, there will be a link to an external file for each item in the list. To make things trickier these files will be password protected.

    Is there anyway I could open the file without inputting the password each time. I could have the password for each item in column B and the links in column C, if that helps things?

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

    Re: Link cells in adjoining sheets if contents match

    You would (AFAIK) need to use VBA for that... possibly using the FollowHyperlink sheet object Event but I have little experience in trying that myself I confess.

  11. #11
    Registered User
    Join Date
    03-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Link cells in adjoining sheets if contents match

    That's certainly a helpful start.

  12. #12
    Registered User
    Join Date
    12-01-2011
    Location
    france
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Link cells in adjoining sheets if contents match

    Quote Originally Posted by DonkeyOte View Post
    You would (AFAIK) need to use VBA for that... possibly using the FollowHyperlink sheet object Event but I have little experience in trying that myself I confess.
    Hello DonkeyOte,

    How could this formula be modified, to put in a macro so that the cell itself becomes clickable instead of having a second collumn?

    Thanx in advance,

    whitehat

+ Reply to 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