Excel 2016 - Link to (or reference) sheet within another workbook

Steve -- trying to figure out what you envisage here. Do you want to be able to type suggestions when working on Project 100 which are copied to Project 50?

Or which are transferred to and stored in (and only in) Project 50 and not in Project 100 at all?

If the latter, which is what I think you want, then it may be that the best way to enter the suggestions is via a UserForm that transcribes the suggestion to a destination worksheet whose location can be flexibly set.

Apologies if I've misunderstood what you are after here.

Neil,
Thanks for helping!

OK, I guess I was not clear. In Project 100--> Suggestions, I need a link (ideally in a cell) which I can click on and be taken to Project 50 --> Suggestions.

OK. Try this. Attached are two workbooks, "Project 100.xlsm" and "Project 50.xlsm".

You need to open them with macros enabled, obviously.

When you double-click the red "go to linked project" cell in Project100, VBA code in the "suggestions" worksheet will attempt to open the file specified in the yellow cell.

If it's already open, it will switch to viewing that file.

To change the file specified, double-click the yellow cell, which will open a file-picker dialogue. Or you can change it manually.

Obviously, right now, the yellow cell is set to some folder on my PC, so you need to change it. If you specify a non-existent file, you will trigger a warning message, and nothing will open.

Both the clickable cells have names: "cOpenLink" and "cFilePath". So you can move them elsewhere in the sheet (so long as you cut and paste them) without damaging the code in any way.

Here is the code in question:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim fd As FileDialog
Dim sPath As String, sFile As String
Dim wb As Workbook
Dim isOpen As Boolean

'depending on which cell is double-clicked:
Select Case Target.Address

    Case [cfilepath].Address
        'open a filedialogue to select a file to link to
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
        .Title = "Select file to open"
        .AllowMultiSelect = False
        'store new file name in cell named "cFilePath"
        If .Show = True Then
        [cfilepath] = .SelectedItems(1)
        End If
        End With
        
    
    Case [cOpenLink].Address
        'open the specified file
        sPath = [cfilepath]
        sFile = Dir(sPath)
        
        'check if file already open
        For Each wb In Application.Workbooks
            If wb.Name = sFile Then
            isOpen = True
            Exit For
            End If
        Next
        
        'If file is not open, open it
        If (Not isOpen) Then
            'check file exists
            Set wb = Nothing
            If Dir(sPath) <> "" Then
            Set wb = Application.Workbooks.Open(sPath)
            Else
            MsgBox ("Linked file does not exist")
            End If
        End If
        
        If Not (wb Is Nothing) Then
        wb.Activate
        wb.Worksheets("SUGGESTIONS").Activate
        End If
    Case Else
        'do nothing
End Select


End Sub

Select allOpen in new window

Hope this is kind of what you want.project100.xlsm
project50.xlsm

Hey Neil,
Wow. The above is MORE than I asked for. :)  That said, I'd rather avoid using macro-enabled workbooks b/c this is probably a one-time need for me. So, I'd settle for simply a link to the other workbook, without referring to a specific sheet within the workbook. That can be done, right?

Apologies to put you through all that effort when it was more than I needed... :)

Thanks.

Got it! Thank you. I ended up choosing A180 b/c that's where the data for Project 100 begins. :) Will close question momentarily.

Thank you again. I tagged two of your comments as best and assisted solutions. I also marked my own comment in between those two posts b/c I felt the follow-up question I posed might be helpful to anyone reading your solution.

Very neat solution!