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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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,041
Messages
5,484,336
Members
407,436
Latest member
Szafranski

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top