How do I reference a worksheet name in a cell, or vice versa?

[Guest]

If I want to label worksheet by date for example, and I want the date to show
in cell A1, how can I either have cell A1 produce the worksheet tab name, or
reflect that name within the cell?

 

[JE McGimpsey]

take a look at


http://mcgimpsey.com/excel/formulae/cell_function.html

 

[Gord Dibben]

To have the worksheet tab name follow the cell value.......

From Bob Phillips..........

Private Sub Worksheet_Change(ByVal Target As Range)
'autoname the worksheet Tab from value in A5
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A5")) 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

Alternative on a button or shortcut key.

Sub SheetName()
ActiveSheet.Name = Range("A5")
End Sub

To have the cell value follow the worksheet tab name see Bob's site....

http://www.xldynamic.com/source/xld.xlFAQ0002.html


Gord Dibben Excel MVP