Help writing a hyperlink reference to another worksheet based on a number in a neighboring cell

[J Blizzard]

I'm using Excel 365 with Windows 10. I want to create links inside my excel file and am having a lot of trouble combining different formulas inside one cell to make a link.

I have one workbook named "Trade Log" with two worksheets named "Trade Log" and "Screenshots." In the Trade Log worksheet I have details about different stock market trades I've made, and I use the Screenshots worksheet to save one or more screenshots of each trade. I want to provide a clickable hyperlink from screenshot #10 in Screenshots to trade #10 in Trade Log, for example. So just a link to click back and forth between any given trade.

The Screenshots sheet has two areas that I will be dealing with. Column B will have a number denoting what number trade it is, and Column C will have a link that takes you back to the corresponding trade in the Trade Log sheet.

A manually-typed example of the Screenshots worksheet:
For my first trade, I will paste a screenshot in cell A1, type "1" in cell B4, and have a link in cell C5 to take me to Trade Log worksheet, cell A3.
For my 2nd trade, I will paste a screenshot in cell A29, type "2" in cell B31, and have a link in cell C31 to take me to Trade Log worksheet, cell A4.
For my 3rd trade, I will paste a screenshot in cell A56, type "3" in cell B58, and have a link in cell C58 to take me to Trade Log worksheet, cell A5.

I want to make a hyperlink to the Trade Log sheet in the same workbook, absolute reference column A, relative reference consisting of the number typed in the neighboring cell + 2.
This is the link I have now but it returns the error, "Reference isn't valid."
=HYPERLINK("#'Trade Log'!INDIRECT(ADDRESS($B4+2,1,3))","Go")
I've tried a lot of combinations of &, +, and lots of quotes and parenthesis but cannot seem to figure this one out and would appreciate some help with it.

Also, I would love to have this be more automated so that I can paste a screenshot, type the trade #, and have a link automatically appear next to it. My first thought is to have column C be full of IF(NOT(ISBLANK()))'s for thousands of cells so that every time I type a number in column B, a link pops up with the correct row reference to the Trade Log sheet. Is this a viable solution? Will this cause excel to make thousands of IF checks every time I update a cell? Is there a better solution?

Thanks for the help, I really appreciate it.

 

[offthelip]

Put this code in the worksheet change event subroutine of your Screenshots worksheet and it will automatically create a link to the TRADE LOG column A row 2 plus the number you entered in column B of screenshots worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("B:B")) Is Nothing) Then
    If IsNumeric(Target.Value) Then
    rowno = Target.Row
    colno = 2
     Application.EnableEvents = False
       With ActiveSheet
           celno = "'Trade Log'!A" & Target.Value + 2
                  .Hyperlinks.Add Anchor:=.Range(Cells(rowno, colno), Cells(rowno, colno)), _
                   Address:="", _
                  SubAddress:=celno
        End With
    End If
End If
Application.EnableEvents = True

End Sub

Note it creates the link in column B not column C, so the trade number becomes a link
you can use the same technique to create the link back too.

 

[J Blizzard]

Thanks for the response, but it only sort of works. If I type a number in a cell in column B, it makea a clickable link to the correct place. But if I then delete the number, the cell doesn't stay blank -- it says 'Trade Log'!A2 and links to cell A2 in the Trade Log sheet. Do I need to create another check to see if the cell is blank, then leave it blank? Or can we start considering blank to be non-numeric? I have never done VBA before. This is all very cool!

 

[J Blizzard]

Sorry for the double post but I think I found a solution for empty cells being classified as numeric. I changed your code slightly:

Old Code: If IsNumeric(Target.Value) Then
New Code: If IsNumeric("+" & Target.Value) Then

So now when I put a number in, it gets evaluated as +1 or +2 or +100, then linked to the appropriate cell. Empty cells get evaluated as + instead of null, so they are not numeric anymore and never become links. Is this too "hackish?"

 

[offthelip]

That is a perfectly good solution, well done.