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?

 

[Joe4]

See here: https://exceljet.net/formula/get-sheet-name-only

 

[Scott Huish]

It doesn't say this in that link but if you put this in before you save the workbook, it won't return anything. If that's the case, save the workbook and then hit F9 to recalculate.

 

[DanteAmor]

In the link is this formula.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

But reference A1 is not necessary, 255 is not necessary either, since the maximum sheet length is 31, so it can look like this:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)

 

[MARK858]

You need to have the A1 (or any cell on the worksheet) else if you are on another sheet when it calculates it will give you the name of the activesheet in the cell.

 

[DanteAmor]

You need to have the A1 (or any cell on the worksheet) else if you are on another sheet when it calculates it will give you the name of the activesheet in the cell.

Okay, but it's only necessary in the first part, In the second part, all the sheets are in the same file

=MID(CELL("filename",A1),FIND("]",CELL("filename"))+1,31)

 

[Peter_SSs]

Okay, but it's only necessary in the first part,

=MID(CELL("filename",A1),FIND("]",CELL("filename"))+1,31)

I disagree. The cell reference should be included in the second part as well. If not, that blue part will return the file name of the most recent file that was calculated file in which the most recent cell was changed** and that may not be the file this formula is in. Therefore the FIND() in this formula could be finding the position of the "]" in the wrong file name.


On a side note, the issue of 255 v 31 could also be resolve by using REPLACE instead.

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

** From the CELL function Help

Syntax
CELL(info_type, [reference])

The CELL function syntax has the following arguments:

Argument

Description

info_type

Required

A text value that specifies what type of cell information you want to return. The following list shows the possible values of the Info_type argument and the corresponding results.

reference

Optional

The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

 

[DanteAmor]

I disagree. The cell reference should be included in the second part as well. If not, that blue part will return the file name of the file in which the most recent cell was changed**
** From the CELL function Help

It doesn't matter, the name of the file doesn't matter, it's always going to end with the "]" character. We do not look for the name of the book, we look for the name of the sheet. At least in this thread.


Also with the following we save second parts.
=TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT(" ",31)),31))

 

[Peter_SSs]

It doesn't matter,

Then how do you account for this?

<b>Sheet2</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; ">Sheet2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">et2</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 >A1</td><td >=MID(CELL<span style=' color:008000; '>("filename",A1)</span>,FIND<span style=' color:008000; '>("]",CELL<span style=' color:#0000ff; '>("filename",A1)</span>)</span>+1,255)</td></tr><tr><td >A2</td><td >=MID(CELL<span style=' color:008000; '>("filename",A1)</span>,FIND<span style=' color:008000; '>("]",CELL<span style=' color:#0000ff; '>("filename")</span>)</span>+1,255)</td></tr></table></td></tr></table>


To achieve this I opened file "Test1" that contains several sheets including this Sheet2 shown above. At that point A1 and A2 both showed 'Sheet2'.
I then opened another file "Testing2" and on 'Sheet5' in that workbook I entered a value in a cell. Cell A2 in Test1/Sheet2 changed from Sheet2 to what you see above. Does that not happen for you?

 

[DanteAmor]

Thanks Peter for the example @MARK858 and you are right, at least I can keep the number 31