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

gravethief13

New Member
Joined
Jan 20, 2020
Messages
2
Office Version
2007
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
L

Legacy 456129

Guest
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.

VBA Code:
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

New Member
Joined
Jan 20, 2020
Messages
2
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,573
Messages
5,487,638
Members
407,606
Latest member
PGMouton

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top