Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Hyperlinking Moving Cell references

  1. #1
    Seeker berz's Avatar
    Join Date
    Jan 2013
    Location
    UK, Nottingham
    Posts
    10
    Articles
    0

    Hyperlinking Moving Cell references



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I did a quick search through the forums and the interwebs but I wasn't able to find a solution (or atleast one i understood) to my problem.

    Context:
    I am currently putting together a register/list of items on sheet 1. Essentially coloumn A will have the major category, coloumn B the subcategory, and coloumn C subsubcategory.

    On sheet 2 I will have a definition provided for each of the items on sheet 1. so coloumn A will be the term/item, coloumn B its definition.

    What I want:
    I currently want to hyperlink each item on sheet 1, to its definition cell on sheet 2. This is perfectly straight forward; i just use hyperlink function and link it to the other sheet, then type in the cell reference.

    my problem:

    My problem is that whenever there is any cell movement on sheet 2, either because i inserted a new row or deleted it, the cell reference for the hyperlink remains the same, even though the cells (and therefore definition) have shifted.

    my question:
    is it possible to create a more dynamic hyperlink that will cater for movement on the other sheet. Or will I be forced to do all my hyperlinks post-definition writing, and the protect the sheet from editing?

    Thanks in advance for taking the time to read over my problem, and any help is welcome.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    We should be able to create a more dynamic Hyperlink or, at worst, give you some code to update them.

    Can you attach a sample workbook that demonstrates the issue?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker berz's Avatar
    Join Date
    Jan 2013
    Location
    UK, Nottingham
    Posts
    10
    Articles
    0
    I have attached a quick sample workbook I cobbled together of the issue.
    sample hyperlink struggle.xlsx

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Probably easiest to do this via a Macro:

    Code:
    Public Sub UpdateHyperLinks()
        
        Dim wsSource As Worksheet
        Dim wsLinkTo As Worksheet
        Dim cl As Range
        Dim rngSearch As Range
        Dim rngLinkTo As Range
        
        'Determine range with hyperlinks
        Set wsSource = ActiveSheet
        With wsSource
            Set rngSearch = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
        End With
        
        'Determine range to look up
        Set wsLinkTo = Worksheets("Sheet2")
        With wsLinkTo
            Set rngSearch = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        End With
        
        For Each cl In rngSearch
            If cl.Hyperlinks.Count > 0 Then
                'Replace the hyperlink
                cl.Hyperlinks(1).SubAddress = wsLinkTo.Name & "!" & Replace(FindRange(cl.Text, rngSearch), "$", "")
            Else
                'No hyperlink, so ignore
            End If
        Next cl
    End Sub
    Private Function FindRange(sText As String, rngLookIn As Range)
        Dim rngFound As Range
        On Error Resume Next
        With rngLookIn
            Set rngFound = .Find(What:=sText, After:=.Cells(1, 1), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
            On Error GoTo 0
            If Not rngFound Is Nothing Then FindRange = rngFound.Address
        End With
    End Function
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker berz's Avatar
    Join Date
    Jan 2013
    Location
    UK, Nottingham
    Posts
    10
    Articles
    0
    Thanks for the reply. I need to ask how to I put in the VBA code? i tried following the link in your signature but it didnt send me to an active page. I tried googling and made it all the way to the developers tab but I couldnt figure out what to do after that.

    Second a quick question on the macro, once its added in do I then use my new macro button to hyperlink between cells?
    sorry I am not the best with software when it comes to the behind the scenes programming

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Berz,

    My apologies! I fixed the link in my signature. Could have sworn it worked before.

    Give that a go and see if you can follow it through now.

    With regards to adding new items, insert the new records in the target sheet, and link any new ones you create from the main page. Then run the macro again and it will re-link all the others based on name. It only works to reset the links on existing items though, which is why you need an active link in the first place.

    If you'd like a macro to add new items to both sheets, we can do that, but we'd need to figure out exaclty what steps you want it to follow.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Seeker berz's Avatar
    Join Date
    Jan 2013
    Location
    UK, Nottingham
    Posts
    10
    Articles
    0
    Hi Ken,
    I followed through with how to insert a VBA code without issue. I have it set up without hitch as a macro (from what i can tell), however hyperlinks still seem to remain static with the addition of new cells.

    just to make sure I am not makeing a minor mistake; I follow through the tutorial, setting up a new vba module with the suggested code. I make sure my items are hyperlinked first then i run the macro. Afterwards i try inserting a few new rows however the hyperlinks remain linking a particular cell and doesnt shift with the correct term it was linked to.

    I am happy with the macro working as a simple update to make them dynamic, no need to go through a lot of effort making them dynamic from the start.

  8. #8
    Seeker berz's Avatar
    Join Date
    Jan 2013
    Location
    UK, Nottingham
    Posts
    10
    Articles
    0
    quick little post: This is for a work related matter, so I wont be active till monday again as I personally don't own a copy of excel at home. (i just use a basic and free spreadsheet software)

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Right, each time you insert new rows on the other worksheet you need to run the macro again to rebuild the hyperlinks. Is that not working?

    (FYI, press Alt+F8 to get to the macro dialog to run it easily. We can also link it to a button if you prefer.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Seeker berz's Avatar
    Join Date
    Jan 2013
    Location
    UK, Nottingham
    Posts
    10
    Articles
    0
    Good morning! hope you had a good weekend

    I tried as you said, inserted new row and then running the macro. but their didnt fix hyperlinks, they continue to link to the same cell reference.

    Is it because of how i am hyperlinking? I go through the following steps:
    right click --> Hyperlink
    "place in this document"
    i type the cell reference i.e. A3
    Select Sheet2 as the reference sheet
    ok

    hope this helps

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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