How to insert in cell A1 the name on the tab

andreascostas

Board Regular
Joined
Jan 11, 2011
Messages
140
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?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,895
Office Version
365, 2010
Platform
Windows
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,695
Office Version
365, 2010
Platform
Windows, Mobile
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,530
Office Version
365
Platform
Windows
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.
Code:
=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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,530
Office Version
365
Platform
Windows
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?
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
:banghead: Thanks Peter for the example @MARK858 and you are right, at least I can keep the number 31 :LOL:
 

Forum statistics

Threads
1,084,748
Messages
5,379,616
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top