Use macro to dynamically insert static formula reference to an Excel sheet

I am trying to create a maintenance log for various pieces of equipment, where there is a sheet with a detailed log for each piece, and an overview sheet with some basic info. I have a macro set up to create a new sheet and enter the necessary data from a userform.

I am also trying to get this macro to insert a new row on the overview sheet with references to this new equipment sheet on the overview with a formula like ='sheetname'!cell. The problem is the formula is being placed in the cell using a variable, so every time I add a new sheet, every instance of the formula updates to reference the newest sheet. How can I input my sheet name variable as just text, or otherwise break the link between the formula and variable?

For reference, here is the code I am using to try and create the reference.

Edit: I have added all of the code for what I am trying to do. Since I have been messing with it more, I have realized that not only the variables are updating in the cell references, but the entire formula is updating. So, both the sheet name and cell reference are changing with each use of the macro.

Sub SubmitBttn_Click()

'Define Variables
    model = modeltxtbx.Value
    ser = serialtxtbx.Value
    loc = loctxtbx.Value
    frq = frqtxtbx.Value
    oil = OilBox.Value
    
    
    
    Call CheckExisting(model, ser, loc, frq, oil)
    
End Sub

Sub CheckExisting(model, ser, loc, frq, oil)
'Check For Entry
    If model = "" Then
        
        'err = MsgBox("Please Enter Pump Model", vbCritical)
       ' If Response = vbOK Then
           ' modeltxtbx.SetFocus
            
        
    End If

'Check For existing sheet
    For i = 2 To Worksheets.count - 1
        If InStr(1, Worksheets(i).Name, model) > 0 Then
            If Worksheets(i).Range("E1").Value = ser Then
                Worksheets(i).Activate
                Beep
                dup = True
                err = MsgBox("This Pump Model and Serial No. Already Exist. Return to Form?", vbYesNo)
                
                Select Case err
                Case Is = vbYes
                    serialtxtbx.SetFocus
                Case Is = vbNo
                    Unload AddPumpLog
                    Exit Sub
                End Select
            End If
        End If
    Next i
    
    
    Select Case dup
        Case Is = True
            Unload Me
        Case Is = False
            Call AddPump(model, ser, loc, frq, oil)
            Unload Me
    End Select
End Sub
    
Public Sub AddPump(model, ser, loc, frq, oil)
'Add new sheet
    Sheets("Template").Select
    Sheets("Template").Copy After:=Worksheets(Worksheets.count)
    
'Move Template to End
    Worksheets("Template").Move After:=Worksheets(Worksheets.count)
    Worksheets("Overview").Activate
'Rename Sheet

    'Count Duplicate Names
    x = 0
    For i = 2 To Worksheets.count - 1
        If InStr(1, Worksheets(i).Name, model) > 0 Then
            x = x + 1
        End If
    Next i
    'Name based on
    'If x = 0 Then
        'nom = model
    'End If
        
    'If Not x = 0 Then
       nom = model & "(" & x + 1 & ")"
   ' End If
            
    'Rename Sheet
    Sheets("Template (2)").Name = nom
        
    'Populate data
    Sheets(nom).Range("B1").Value = loc
    Sheets(nom).Range("D1").Value = nom
    Sheets(nom).Range("E1").Value = ser
    Sheets(nom).Range("F1").Value = frq
    Sheets(nom).Range("C1").Value = oil
    
'Add To Overview
    'Add Row to Overview
    Worksheets("Overview").ListObjects("OverviewTable").ListRows.Add
    r = Sheets("Overview").Cells(Sheet1.Rows.count, 1).End(xlUp).Row
    

    
    
    'Add Linked Values
    Sheets("Overview").Cells(r, 1).Value = "='" & nom & "'!$D$1"
    Sheets("Overview").Cells(r, 2).Value = "='" & nom & "'!$E$1"
    Sheets("Overview").Cells(r, 3).Formula = "='" & nom & "'!$C$4"
    Sheets("Overview").Cells(r, 4).Value = "='" & nom & "'!$E$4"
    Sheets("Overview").Cells(r, 5).Value = "='" & nom & "'!$A$"
    Sheets("Overview").Cells(r, 6).Value = "='" & nom & "'!$B$"
    Sheets("Overview").Cells(r, 7).Value = "='" & nom & "'!$D$"

    


     
    
    Set t = ActiveSheet.Cells(r, 8)
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
        btn.OnAction = "LogEntry"
    With btn
        .Characters.Text = "Add Log Entry"
        .Name = "AddEntry"
    End With
    
    
    'Add Show Log Button
    Set t = ActiveSheet.Cells(r, 9)
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With btn
      .OnAction = "ShowLog"
      .Caption = "ShowLog"
      .Name = "Showlg"
      
    End With
    
     
    Unload Me
End Sub

Solution

You can set the formula through the `Formula' property:

Dim nom as String
Sheets("Overview").Cells(r, 2).Formula = "='" & nom & "'!$E$1"

The problem you further met is related to Excel tables (aka ListObjects in VBA). If you enter a formula into the table, the formula applies to the whole column.

Below I explain how to remove the table from the sheet but consider the recommendation that you don't need to use formulas in the Overview table since these data are constant (almost?).

To remove the table object and work with data as with the normal range:

In "Overview" sheet, in "Name Box", select "OverviewTable".
Right-click on the selected cells and command "Table-->Convert to Range" then confirm.
Apply the autofitler.
In the code, comment the line which refers ListObject and increment r by 1:

    ' Worksheets("Overview").ListObjects("OverviewTable").ListRows.Add
    r = Sheets("Overview").Cells(Sheet1.Rows.Count, 1).End(xlUp).Row + 1

So, there are two solutions for your choice for the Overview table:

Use constants.
Convert the table to the range.