Sheet Names

If you are referencing to a cell on another worksheet other than the sheet the formula is on put the worksheet then the cell

On sheet one in any cell put fomula =SUM(Sheet2!A1)
What ever is in sheet 2, cell A1 will be the answer.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCell As Range, check As String
Set theCell = Range("A1")
Application.EnableEvents = False
If Not Intersect(theCell, Target) Is Nothing Then
check = MsgBox("Do you want this sheet named as the entry in " _
& theCell.Address(False, False) & "?", vbYesNo)
If check = vbNo Then
theCell.Value = ActiveSheet.Name
Application.EnableEvents = True
Exit Sub
End If
End If
On Error Resume Next
ActiveSheet.Name = theCell.Value
If Err.Number <> 0 Then
GoTo ErrH
End If
On Error GoTo 0
Application.EnableEvents = True
Exit Sub
ErrH:
MsgBox "The data that was input to " _
& theCell.Address(False, False) _
& " is not a valid name for a worksheet." _
& " Input another name."
theCell.Value = ActiveSheet.Name
theCell.Select
Application.EnableEvents = True
End Sub