Hyperlink to cell using formula result

[ratherbgolfing]

I am trying to use the following formula to hyperlink to a cell in another workbook.

I am looking up the cell I want to go to using
=ADDRESS(MATCH(A15,[LCIREDFLAGNOTES.xlsx]LCI!$A:$A,0),1,4,1)

Then I want to hyperlink to that cell in that workbook. I have tried alot of different stuff but the one I keep coming back to is

=HYPERLINK("L:\Reports\redflag\lciredflagnotes.xlsx"ADDRESS(MATCH(A15,[LCIREDFLAGNOTES.xlsx]LCI!$A:$A,0),1,4,1)

Which obviously doesn't work. I need to lookup the cell that matches a value and hyperlink to the corresponding cell in the other workbook. Any help would be great.

 

[Randall00]

*thumbs up* Some luck at least!

Okay, a little trickier than I anticipated, but here's one way, sorta:

Sub LinkToCellValue()

If IsEmpty(ActiveCell) = True Then
    Exit Sub
Else

    Dim cellAddress, WBpath As String
    
        cellAddress = ActiveCell.Value
        
        WBpath = ""L:\Reports\redflag\lciredflagnotes.xlsx"
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=WBpath
            ThisWorkbook.FollowHyperlink WBpath
    
    Dim TargetWB As Workbook
    
        Set TargetWB = Workbooks("lciredflagnotes.xlsx")
            TargetWB.Activate
        
    Dim TargetSheet As Worksheet
    
        Set TargetSheet = TargetWB.Sheets(1)
            Application.Goto TargetSheet.Range(cellAddress), scroll:=True
End If

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 8 Then
    Cancel = True
    LinkToCellValue
Else
    Exit Sub
End If

End Sub

The thing is, there's no hyperlinks involved here, but this will achieve a similar effect that hopefully does what you need it to.

Right-click on the sheet with the address formulas on it, click "View Code" and copy and paste that mess of text right into it.

What it actually does is that instead of hyperlinking to the cell, you have to double-click on it. Double-clicking on one of the address formulas will open the target workbook and essentially "scroll" to the cell reference.

Getting it to work properly is contingent on a couple things:

- The target workbook only has one worksheet that you want to link to.

- The source workbook is macro-enabled and your Trust Center settings allow for macros to run.

- The address cell that you are double-clicking must be in column H and must contain a valid cell reference. Which it should if they're all address formulas.

Let me know if that makes sense and/or fails to work like I describe. Or if you really need them to be hyperlinks instead of double-clickable "invisible" links, it looks like there's a way to do that too, but it just takes some more digging.

Here's hopin'!

 

[ratherbgolfing]

Thanks again... I ran the code and I'm getting a syntax error on the wb. Do I need the fully qualified path? It is a network location.

 

[Randall00]

Whoops, couple of typos in there. Plus a totally unnecessary line that was adding unnecessary hyperlinks. Try this one:

Sub LinkToCellValue()

If IsEmpty(ActiveCell) = True Then
    Exit Sub
Else

    Dim cellAddress, WBpath As String
    
        cellAddress = ActiveCell.Value
        
        WBpath = "L:\Reports\redflag\lciredflagnotes.xlsx"
            ThisWorkbook.FollowHyperlink WBpath
    
    Dim TargetWB As Workbook
    
        Set TargetWB = Workbooks("lciredflagnotes.xlsx")
            TargetWB.Activate
        
    Dim TargetSheet As Worksheet
    
        Set TargetSheet = TargetWB.Sheets(1)
            Application.Goto TargetSheet.Range(cellAddress), scroll:=True
End If

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 8 Then
    Cancel = True
    LinkToCellValue
Else
    Exit Sub
End If

End Sub

 

[ratherbgolfing]

Works perfectly! Is there a way that I can give the cell with the address formula in it an alias. Now it just shows the corresponding cell like $A$234. I would like to have it say NOTES.

 

[Randall00]

Yup, probably, but like all these things...it depends. Is there anything in Column I? Or I guess, what is the first blank column to the right of Column H?

 

[ratherbgolfing]

Well I actually added a couple of colums to the report so I had to modify your code to check column 10. The next empty column would be column 11.

 

[Randall00]

Alrighty, well in that case, I'd just put the word NOTES into Column 11 and then hide column 10 with all the cell references.

Then add this on a new line just after 'Sub LinkToCellValue()':

Dim HiddenCell As Range

Set HiddenCell = ActiveCell.Offset(0,-1)

Then replace ActiveCell with HiddenCell everywhere else in the code (there should only be two) , make sure the bottom part says Target.Column = 11 instead of 10 and it should work pretty nicely.

 

[ratherbgolfing]

Worked perfectly. Thanks for all of your help!

 

[Randall00]

Right on! This whole website has offered me so many solutions to so many problems--it's nice to be able to contribute one, too.