Sheet tab name appearing in a cell

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
62
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
L

Legacy 63186

Guest
Insert the following code into a module.

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

Then enter =SheetName(A1) into any cell.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

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

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

HTH, Andrew
 

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
62
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :

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
 

Forum statistics

Threads
1,141,626
Messages
5,707,491
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top