How to insert in cell A1 the name on the tab

[andreascostas]

Is there a way to insert the name on the tab of an excel sheet into cell A1
without having to type it every time?

 

[Peter_SSs]

.. at least I can keep the number 31

Yes you can.

However, while testing for this thread, I think I have stumbled across another bug/feature(!#) of this use of the CELL() function that as yet I have not noticed documented anywhere. Interested if others can reproduce this or have any further information.

1. Start a fresh workbook and save it as abc.xlsx
2. Ensure the workbook has at least 2 worksheets
3. Name one of the sheets 'abc'. That is exactly the same as the file name without the file extension.
4. In A1 enter the A1 formula from post 9. Formula should correctly return 'abc' in A1
5. Delete all other worksheets from the workbook except 'abc'. For me, this results in a #VALUE ! error in A1.
On stepping through the formula evaluation, the problem is that the text from CELL("filename",A1) does not include the sheet name nor put the file name in [], hence the FIND() results in the error.
Pressing F9 or reconfirming the A1 formula or saving the workbook or closing/reopening or entering a new value somewhere in the worksheet does not change the error value in A1.

6. If I now add a second worksheet, depending on how I did that, A1 may now again return the sheet name or it may still contain the error. If still the error, now F9 or entering a value anywhere in the sheet results in the correct evaluation in A1.

Strange?

Unless there is something particular happening for me, to have a robust formula to return the sheet name, it might need to be this whopper or similar:

=IFERROR(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(
CELL("filename",A1),".",REPT("?",5)),LEN(SUBSTITUTE(CELL("filename",A1),".",REPT("?",5)))-5),"?",""),"\",REPT("?",31)),31),"?",""))

 

[MARK858]

Interested if others can reproduce this or have any further information.....

Unless there is something particular happening for me

Hi Peter, not just you I get the same behaviour.

 

[Peter_SSs]

Hi Peter, not just you I get the same behaviour.

Hi Mark, Thanks for testing and the confirmation.

 

[DanteAmor]

How about an UDF

Function sheetname()
  sheetname = ThisWorkbook.ActiveSheet.Name
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TextOnly just like it was a built-in Excel function. For example, in cell A1:

=sheetname()

 

[Peter_SSs]

How about an UDF

Function sheetname()
  sheetname = ThisWorkbook.ActiveSheet.Name
End Function

What will cause the function to update if the sheet name is changed?

.. and in any case, if it does update while the sheet it is on is not the active one it will return the incorrect name.

 

[DanteAmor]

The same problem , haha,

then like this:

Function sheetname(r As Range)
  sheetname = ThisWorkbook.ActiveSheet.Name
End Function

Use:

=sheetname(A2)

 

[Peter_SSs]

then like this:

Function sheetname(r As Range)
  sheetname = ThisWorkbook.ActiveSheet.Name
End Function

Use:

=sheetname(A2)

Same question

What will cause the function to update if the sheet name is changed?

Here I have changed the sheet name from "def" to "ghj"
https://www.dropbox.com/s/jhb72v7cn8za767/sheet%20name%201.png?dl=0

 

[DanteAmor]

Same question

Okay, I did the tests in post #11 (Repeatedly) and I think this one works.

Function sheetname(r As Range)
  Dim l As Workbook, s  As Worksheet
  Set l = ThisWorkbook
  Set s = l.ActiveSheet
  sheetname = s.Name
End Function

 

[Peter_SSs]

You aren't addressing my question from above.
Changing the worksheet tab name does not trigger the function to recalculate. So exactly the same thing (nothing) happens when the worksheet tab name is changed. (That may possibly depend on what else is in the sheet/workbook, but it is what happens in my test workbook)

Further, my other earlier point still stands:
".. and in any case, if it does update while the sheet it is on is not the active one it will return the incorrect name."

Here is my sheet with "aa" in J1 of Sheet6 and I enter the formula in A2 as shown. Correct result.

<b>qqq</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">qqqaa</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=sheetname(A2)&Sheet6!J1</td></tr></table></td></tr></table>

Now here is my sheet immediately after I entered "dd" in cell J1 of Sheet6. Incorrect result.

<b>qqq</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Sheet6dd</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=sheetname(A2)&Sheet6!J1</td></tr></table></td></tr></table>


This may be closer, but I'm still not 100% sure

Function sheet_name(r As Range)
  Application.Volatile
  sheet_name = r.Parent.Name
End Function

 

[DanteAmor]

What will cause the function to update if the sheet name is changed?

.. and in any case, if it does update while the sheet it is on is not the active one it will return the incorrect name.

I don't know the cause, as well as many other excel issues.