The formula =CELL("filename",A1) returns the full path, file name and worksheet name for the worksheet currently displayed. The spreadsheet name is enclosed in square brackets and the worksheet name is after the ] square bracket. The formula above gets the full path and file name, and then only uses the rightmost characters to display the sheet name. How many characters are displayed depends on the location of the ] square bracket. To get the n rightmost characters, we take the length of the string (using len) less the position of the ] square bracket which is found using the SEARCH function. So what is left is just the worksheet name.
In answer to your second question, you will need to use VBA code. Right click the tab of the worksheet and click the option 'View Code' to open the VBA editor screen and enter the following code :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strOldName As String
If Target.Address = "$A$1" Then
strOldName = Range("A1").Parent.Name
Sheets(strOldName).Name = Range("A1").Value
I have assumed the new sheet name is in cell A1. Change the $A$1 and "A1" parts to the actual cell reference you want to use. Save and close the VBA editor screen. Now when you change the value in (say) cell A1, the worksheet name will automatically change.