Forum Discussion
Hyperlink and view the cell in different workbooks
Hi Experts,
I'd like some help with this, i have 2 workbooks, namely Test 1 and Test 2 ..
Excel file (Test 1)
| TEST 1 | 100 |
| TEST 2 | 200 |
| TEST 3 | 300 |
Excel file (Test 2)
TEST A | 300 |
| TEST B | 200 |
| TEST C | 100 |
I'd like a formula whereby if i click "100" cell from (Excel file 1) it should open the (Excel file 2) and take me to it's cell with same value as excel file 1.
Thank you.
2 Replies
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.
