Hyperlink that links to a cell relative to the sheet the hyperlink is located in

[gravethief13]

Hi,

I'm creating an excel sheet for a friend who is even less well versed in excel than I am and I'm having trouble making a hyperlink that just points to a cell, no matter what sheet that the hyperlink is in. Here's an example:

I have a workbook with one sheet in it, Sheet1. In cell A1 of Sheet1 I create a hyperlink to cell J1 of Sheet1. I want to be able to make a copy of Sheet1 called Sheet2, and have the copied hyperlink point to cell J1 of Sheet2 without me having to manually edit it. By default, the newly copied hyperlink in Sheet2 will point to cell J1 of Sheet1.

The reason for this is that I'm making a template sheet that can be easily copied without any real modifications needing to be made to it besides renaming a few things. The template sheet has many hyperlinks on it for the purpose of navigation, so if there's any other way to create links for navigation that are relative I'd use them. I tried using the formula =HYPERLINK($J$1,"Test"), but it gives me a "Cannot open the specified file" error.

Any help is apprenticed, I've been at this for two and a half hours now.

 

[Legacy 456129]

Only 2.5 hours at it ;? So where in the formula are you pointing to Sheet1?

Providing some sample code, this one works for me, and when it resolves to not finding anything, goes to show an empty string, meaning there were no results that produced a specific error.... all's well.

=IFERROR(HYPERLINK("#"&ResShtOut&"!B"&[@Record]+1&":AC"&[@Record]+1,@INDEX(ResOut[Name],[@Record])),"")

If it does, a balloon hover.over appears with where it is pointing, a filter is applied use a line number that's is calculated in the the @Record column of the table, then selects the row from col B to AC in the target sheet. When it lands on the target sheet, a sheet module Activate code executes a procedure, looks in col 12 of the landed row for the code and then sticks that the table filter which gives me a desired report of items that produced a certain error when the results completed.

Private Sub Worksheet_Activate() 'ver 24.04.2019

    Run "MaxSheet"
    ActiveWindow.DisplayHeadings = True
    Me.ScrollArea = "A1:BI1048576" 'Only show user area, Masks range off-limit
    '.DisplayFormulaBar = True
    If ResTarget = True Then 'added 1-12-2016 To execute an unhide of target row if coming from the ResErr sheet
        Application.ScreenUpdating = False
        Dim nr As Long, a As Long, colnr As Long, rownr As Long, exitcd As Long ',vArr As Variant, colltr as string
        colnr = Range("ResOut[Exit]").Column
        'vArr = Split(Cells(1, colnr).Address(True, False), "$") 'from https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter
        'colltr = vArr(0)
        rownr = ActiveCell.Row
        With Me
            If .ListObjects(1).ListRows.count < rownr Then GoTo skip ' add 10.04.2019 to catch random navigation activity
            On Error GoTo skip 'add 24.04.2019 to catch when there are zero errors (mitmatch)
            exitcd = .Cells(rownr, colnr).Value2
            .ListObjects(1).Range.AutoFilter Field:=colnr, Criteria1:=exitcd, Operator:=xlOr 'Exit code
            'For nr = a To a + 10
            '   .Rows(nr & ":" & nr).EntireRow.Hidden = False 'ditto
            'Next nr
        End With
        Application.ScreenUpdating = False
skip: 'add 10.04.2019
        ResTarget = False 'ditto, to set this to false so going to results from anywhere else does not trigger the If loop
    End If 'ditto
    Run "TabColorSet", 4, Me.Name 'moved 1-12-2016 from Worksheet_Change sub, to execute less times.
     ScreenOn
   
End Sub

The code works very well and stable, so, not going to fix if someone sees a suspect line :0)

 

[gravethief13]

Thanks for the reply SekeRob2. Unfortunately I'm clueless about VBA code and can't find in there which part addresses the hyperlinking. I did however just manage to construct a relative hyperlink by using regular functions. It works with sheets that have special characters too. I'll post it here for in case anyone's interested:

=HYPERLINK(LEFT(RIGHT(CELL("filename",A1),(LEN(CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)),FIND("]",RIGHT(CELL("filename",A1),(LEN(CELL("filename",A2))-FIND("[",CELL("filename",A1))+1)))) & "'" & RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) & "'" & "!$A$1","To Test Cell")

You put the cell address you want to link to in place of the red text and you put the text displayed by the hyperlink cell in place of the blue text.