You can use the change event to detect a change in your linked cell. This event will run code that will change the Worksheet name.
The following code is attached to the Worksheet - Right-click the worksheet tab name and select View Code.
In the Visual basic window that opens paste this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
ActiveSheet.Name = Target.Text
From the Visual Basic menu select File - Save
Use Alt+f11 (Alt key and function key #11 pressed together) to return to the main Excel Window
Now, changing the text in cell A1 will change the Tab name.
Note that this is very basic - there are no checks for a duplicate name - which is not allowed, and there are no checks for other non-valid worksheet names such as names containing special characters such as / or \.
You could add additional code to make this more robust.
You can change the linked cell - but note that it must include the $ signs.