Hyperlink and view the cell in different workbooks

You can’t make the original number cell clickable while it references itself – that would cause a circular error.

Instead, keep your numbers in column B and use a helper column for the hyperlinks.

In Test 1.xlsx, put this formula in cell C2 (next to your first number) and copy it down:

=IFERROR(HYPERLINK("'[Test 2.xlsx]Sheet1'!B" & MATCH(B2, '[Test 2.xlsx]Sheet1'!$B:$B, 0), B2), "Not found")

Clicking the number in column C will open Test 2.xlsx and jump to the matching value in column B.
If the files are in a different folder, use the full path with single quotes:

=IFERROR(HYPERLINK("'" & "C:\YourFolder\[Test 2.xlsx]Sheet1'!B" & MATCH(B2, 'C:\YourFolder\[Test 2.xlsx]Sheet1'!$B:$B, 0), B2), "Not found")

Just be sure to replace C:\YourFolder\ with the actual folder location.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

You can use HYPERLINK with MATCH.

If the value in Test 1 is in B2, and the matching values in Test 2 are in column B, try:

=IFERROR(HYPERLINK("[Test 2.xlsx]Sheet1!B"&MATCH(B2,'[Test 2.xlsx]Sheet1'!$B:$B,0),B2),"Not found")

Copy it down for the other values.

Clicking the result should open Test 2.xlsx and jump to the matching value in column B.

If the workbook is in a different folder, you may need to use the full file path.

https://techcommunity.microsoft.com/discussions/excelgeneral/hyperlink-and-view-the-cell-in-different-workbooks/4536253/replies/4537525

Hyperlink can only jump to a cell address, not search for a value.

Macros can search for the value and jump to the correct cell.

If using VBA, place the code in the sheet module, update the file path, and save as .xlsb. and double click on value

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

    Dim val As Variant

    Dim wb As Workbook

    Dim ws As Worksheet

    Dim c As Range

    ' Only run if the double-clicked cell contains a number

    If IsNumeric(Target.Value) Then

        val = Target.Value

        Cancel = True   ' Prevent Excel from entering edit mode

        ' Open Test 2 workbook (update path if needed)

        Set wb = Workbooks.Open("your File path\Test 2.xlsx")

        Set ws = wb.Sheets("Sheet1")   ' Change sheet name if required

        ' Search for the value

        Set c = ws.Cells.Find(What:=val, LookIn:=xlValues, LookAt:=xlWhole)

        If Not c Is Nothing Then

            c.Select

        Else

            MsgBox "Value " & val & " not found in Test 2.", vbInformation

        End If

    End If

End Sub

If using hyperlink, define the named range “Values2” in both workbooks, but note that hyperlink still refers to the cell address, not the value and both files should be in same folder.

=HYPERLINK("[Test 2.xlsx]Sheet1!" & ADDRESS(MATCH(B1,Values2,0), COLUMN(Values2)), "Go to match")