One way:

Put this in your worksheet 1 code module (right-click the worksheet tab
and choose View Code):

   Private Sub Worksheet_Change(ByVal Target As Excel.Range)
       Const sNAMECELL As String = "A1"
       Const sERROR As String = "Invalid worksheet name in cell "
       Dim sSheetName As String
       
       With Target
          If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
             sSheetName = .Parent.Range(sNAMECELL).Value
             If Not sSheetName = "" Then
                On Error Resume Next
                .Parent.Parent.Sheets(2).Name = sSheetName
                On Error GoTo 0
                If Not sSheetName = .Parent.Parent.Sheets(2).Name Then _
                       MsgBox sERROR & sNAMECELL
             End If
          End If
       End With
   End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
  Application.EnableEvents = False
  With Me.Range("A1")
  If .Value <> "" Then
        Sheets(2).Name = Me.Range("A1").Value
   End If
End With
stoppit:
Application.EnableEvents = True
End Sub

Right-click on the tab of Sheet1.  Select "View Code".

Copy/paste the above into the sheet module.

Alt + q to return to Excel.

Choose from the dropdown in A1

Gord Dibben  MS Excel MVP