Not to step on Juan, but VBA would be the only way to do this. If you mean that, for instance, you want the name of the sheet tab for Sheet2 to change when a cell, say A1 in Sheet1 is changed, then enter this code in the module of Sheet1. Note that we need to use the VBA sheet object code in referencing the sheet being changed.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Sheet2.Name = [A1].Value
Here's another example, which will save the old sheet name in the windows registry so it will still work when you come back to it after closing/reopening Excel. Note that you have to run the first part setting the registry variable and initial sheet name only once, then must disable that part from then on. This macro could then be called as a result of opening a workbook or some other event. Hope this helps... Cheers,
'THIS RANAMES A WORKSHEET TO THE VALUE STORED IN A CELL. IT KEEPS TRACK
' OF THE SHEET NAME PERMANENTLY AND RESETS IT EVERY TIME IT IS RENAMED. IT
'ASSUMES YOU ALREADY HAVE A SHEET NAMED "MYSHEETNAME", AND THE FIRST SECTION
'THAT CAPTURES THE SHEET NAME TO THE REGISTRY NEED ONLY BE DONE ONCE AND NEEDS TO
'BE DISABLED OR REMOVED AFTER THE FIRST TIME IT IS RUN.
'******THIS PART NEEDS TO BE DONE ONLY ONCE TO SET INITIAL VALUES*****
OldName = "MYSHEETNAME"
'SETS THE INITIAL VALUE OF THE OLD SHEET NAME
SaveSetting "MyName", "MyProject", "OldSheetName", OldName
'Sets the Windows registry to create the appname (MyName), section
' (MyProject), key (OldSheetName) and setting (OldName). It saves the value of OldName
'in the registry variable "OldSheetName" until it is reset via the same command.
'AFTER THIS PART IS DONE ONCE, THEN DISABLE OR REMOVE IT