Only through VBA.
Event code.......
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
This is sheet event code fired when A1 value changes.
Right-click on your sheet tab and "View Code".
Copy/paste the above into that module.
Gord Dibben Excel MVP