Sheet tab name appearing in a cell

[Shinano]

How do I make the name of a given sheet appear in a cell in the sheet in question? The cell and the tab should be linked for automatic update.

Any suggestion is appreciated.

Thank you.

 

[Legacy 63186]

Insert the following code into a module.

Function SheetName(ref) As String
SheetName = ref.Parent.Name
End Function

Then enter =SheetName(A1) into any cell.

 

[Andrew Fergus]

Hi

There may other ways but try this formula in a cell :

=RIGHT(CELL("Filename",A1), LEN(CELL("Filename",A1)) - SEARCH("]",CELL("Filename",A1)))

HTH, Andrew

 

[Shinano]

First, I guess I should say thank you. -Know that it is a little late, shame on me.

Anyway, Andrew, could you please explain me the details of your formular? Some of if I am ok with, some of it is pure Greek to me.

And another question: Any idea of how to go the other way and have the content of a cell automatically become the sheet name?

Thanks a lot.

Shinano

 

[Andrew Fergus]

Konnichi wa Shinano (I hope I got that right!)

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
End If

End Sub

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.

HTH, Andrew