Hyperlinking Moving Cell references

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
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 :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Berz,

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

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.
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
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.
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
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)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.)
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Ah...

I tested with range hyperlinks, and you're creating worksheet hyperlinks. Replace the first macro with this:

Code:
Public Sub UpdateHyperLinks()
    
    Dim wsSource As Worksheet
    Dim wsLinkTo As Worksheet
    Dim hyp As Hyperlink
    Dim rngSearch As Range
    Dim rngLinkTo As Range
    
    'Determine range with hyperlinks
    Set wsSource = ActiveSheet
    
    '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 hyp In wsSource.Hyperlinks
        'Replace the hyperlink
        'cl.Hyperlinks.Delete
        hyp.SubAddress = wsLinkTo.Name & "!" & Replace(FindRange(wsSource.Range(hyp.Range.Address).Text, rngSearch), "$", "")
    Next hyp
End Sub

(Leave the second macro intact though)
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
Yes! This works without a problem whenever i am adding new rows.
However i tested with adding a new coloumn, and it completly messes up the hyperlinking if i were to insert a new coloumn A for example.
I can't foresee any direct need for me to insert new coloumns at this point though, so its not the end of the world that it cant :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Correct, columns would mess it up. I didn't set the macro up to do columns, but I could if it was necessary.

Do you think you'd ever have that need?
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
Honestly no i cant see a need for them to be dynamic vis-a-vis an addition of coloumns, so dont worry about editing the macro so that includes that. I was just being thorough in testing the uses of the macro.

Thank you so much for all the help! it will make editing this document so much easier and save me hours of work!
 

berz

New member
Joined
Jan 15, 2013
Messages
10
Reaction score
0
Points
0
Location
UK, Nottingham
Hey there, hate to re-open up this thread up 2weeks after i said it all works a-ok

i was doing some changes to the document i was working on, and i have realised that the macro will only re-assign the hyperlink to the correct cell if both cells have identical content.
i.e.the original cell says "liquidity Risk" which i want to hyperlink to the next page
if i link this to a cell that says "liquidity Risk", then running the macro for a change of cell position then it works without problem. however if i were to have linked it to a cell that were simply titled "liquidity" then an error code comes up and that hyperlink no longer works.

Is there an easy fix to this situation? as there are a number of cells which are linked which are not identically named.

Sorry again for the tardiness of my realisation
 
Top