How to Make Sheet HyperLink go to 2nd Screen

Hi again Susan and Happy New Year!

I can only think to do this in VBA and there's probably a better way than this one...

In the first attached file, the first sheet "Table of Contents" (aka TOC) has a single hyperlink in cell A2  which points to cell A5 in the second sheet "Introduction".

If you open a second window (showing TOC) then return to the first window and click A2, this event macro is triggered (with comments for your information)

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim HypWs As String, HypAddr As String

    ' see if there's a second window...
    If ThisWorkbook.Windows.Count > 1 Then
        ' extract worksheet name and cell from hyperlink
        HypWs = Left(Target.SubAddress, InStr(Target.SubAddress, "!") - 1)
        HypAddr = Mid(Target.SubAddress, InStr(Target.SubAddress, "!") + 1, 12)

        Application.EnableEvents = False
        ' go back to TOC on sheet1
         Sheets(1).Activate
         ' move to second window...
         Windows(2).Activate
         ' ... then show the hyperlink's sheet and move to the target address
         With Worksheets(HypWs)
            .Activate
            .Range(HypAddr).Select
         End With
    End If

    Application.EnableEvents = True

End Sube

Window 1 will show TOC and window 2 will be activated with cell A5 selected. 

It works because the "within file" hyperlink (Target.Subaddress) takes the form:

Introduction!A5

You can use this with any other hyperlink on Sheet1 but they need to be that simple form.

Revision 05 January 2024

This responds to your file attached to the Question and your Discussion comment "I noticed that when in Excel, editing the hyperlink and viewing the Cell Reference, some of the sheet names are in quotes, some are not.  Those sheets in quotes will not work with the hyperlink vba code (See hyperlink to "Basic-Dyes")." 

Not sure why that happens but in the SECOND file attached, I've used your file but simply added a line to remove the quoptation marks when the macro extracts the sheet name from the hyperlink (changes in bold in the code extract below):

        ' extract worksheet name and cell from hyperlink
        HypWs = Left(Target.SubAddress, InStr(Target.SubAddress, "!") - 1)
        ' remove any quote marks from sheet name
        HypWs = Replace(HypWs, "'", "")

This should work with all your hyperlinks (though I had to Edit the hyperlink in cell A9 "AY23" to refer to the worksheet). Also I removed the =HYPERLINK formula for Freight and replaced it with a hyperlink link like the rest.. Accordingly I've removed your Module1 (not needed). I also renamed your tab "Sheet1" to "Contents" for better reading (it's still theleftmost sheet so has the code index 1 in Sheets(1) BTW).

Note that to reset the "read" hyperlinks, I selected all the cells. copied them to another column then cut them and pasted them back in column A.

Revision 06 January 2024

In the THIRD attached file, the event procedure above has been modified so

1. if there's just one window, the code opens a new one
2. the second window is moved to the right so column A of the table of contents (TOC) is visible
3. the hyperlinked page is shown on the right (even if the hyperlink was launched form the rightmost window).

Changes are in bold below:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim HypWs As String, HypAddr As String

    ' see if there's a second window...
    If ThisWorkbook.Windows.Count = 1 Then

        ' if only one, create a new one and pisition to the right
        ThisWorkbook.NewWindow
        Windows(1).Left = Windows(1).Left + Sheet1.Columns(1).Width + 15
        ' return left sheet to TOC
        Windows(2).Activate
        Sheets(1).Activate
        ' make rightmost window active
        Windows(2).Activate

        Else
        ' extract worksheet name and cell from hyperlink
        HypWs = Left(Target.SubAddress, InStr(Target.SubAddress, "!") - 1)
        ' remove any quote marks from sheet name
        HypWs = Replace(HypWs, "'", "")
        HypAddr = Mid(Target.SubAddress, InStr(Target.SubAddress, "!") + 1, 30)

        Application.EnableEvents = False
        ' go back to TOC on sheet1
         Sheets(1).Activate
         ' move to second window...
         Windows(2).Activate
         ' move it so TOC column A is visible
         Windows(1).Left = Windows(2).Left + Sheet1.Columns(1).Width + 15
         ' ... then show the hyperlink's sheet and move to the target address
         With Worksheets(HypWs)
            .Activate
            .Range(HypAddr).Select
         End With
    End If

    Application.EnableEvents = True


End Sub

Note that the active window always has index number 1 so is  .Windows(1)

Hope this works for you, If so, please remember to mark this Answer as Selected.