Forum Discussion
workbook and sheet name via formula
Afraid for Excel Online that's only with Office Script, there are properties getName both for workbook and worksheet.
VBA and related functions don't work in Excel Online.
Read out the designation / name of the worksheet
Read out by formula
The focus of the formula solution is the CELL function (infotype; reference). In addition to the file name and the full path, the File Name infotype also returns the name of the worksheet. This is extracted using further functions so that only the sheet name is output at the end.
The result is achieved with these two formulas:
(formulas are from German Translate)
a) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; 255)
or
b) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; LENGTH (CELL ("filename"; A1)) - FIND ("]"; CELL ("filename"; A1)))
2. Read out via VBA
The following VBA function reads out the sheet name via VBA and displays it in a cell. To do this, enter the following code in a code module.
Public Function sheetname () As String
Application.Volatile
sheetname = ActiveSheet.Name
End function
If you now enter the function = sheetname () in a cell, the sheet name is read out and output in the corresponding cell.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
works great but for active worksheet only..actaually i want sheetname in all sheet with its own sheetname. Thank youNikolinoDE
rcstha - you just need to put the formula on each sheet. Just select which environment your workbook has to work in: Desktop or SharePoint/Web.
Desktop:
=MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,32)
(thanks NuggetSA )SharePoint/Web:
=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D2), "!"), "'", "" ), "=", "")
with D2 containing:
='Sheet1'!D1 formatted as 0;0;
The latter is easy to create: place your cursor on D2 type = switch to another sheet, then switch back to Sheet1, and click on D1. Done.Thank youJan_Wijninckx
i am using desktop and got exact answer by using the formula
=TEXTAFTER(CELL("filename",A1),"]")
I guess the German translation wasn't too good. Try this:
=MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,99)
The MID() formula picks an extract from CELL("filename") starting at the FIND() position for the "]" and returning whatever is to the right. If you look at what =CELL('filename",<location>) returns it will help visualise.
The 99 at the end is a lazy parameter assuming there is no more than 99 characters after the "]" was found. It's probably safe but you can increase if you want to or make the formula more complicated by calculating the remaining length. I don't think the additional technical artistry helps understand the formula!
NikolinoDE Before moving to Office 365 I used this function to include the workbook name in the Heading of the Workbook (not in the header) so new versions automatically pick up the right name. It almost works in 365, using =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-6) but as soon as I open a 2nd workbook it changes the content of the cell in the 1st workbook to the filename of the newly opened workbook. Very frustrating and confusing!
You may vote here CELL function added to online for this function. More votes sooner it could appear.
- To get the Sheetname in Excel Online: In another Sheet (e.g. Sheet2)
B1=Sheet1!$A$1
C1=SUBSTITUTE(LEFT(FORMULATEXT(B1);FIND("!";FORMULATEXT(B1)));"=";"")
Result of C1: Sheet1!
The result of C1 can be used in Formulas such as INIDIRECT (…) and adapts automatically if sheet1 is renamed.
And of course, you can place this workaround on a technical hidden sheet (sheet2 in this example) , and display the result on the current sheet, if you really need the current sheet name
Excel
ExcelApplication features Excel for the web Excel desktop app
Feature availabilityThank you for your patience and time.
Wish you a nice day / night with lots of health, joy and love.
Nikolino
I know I don't know anything (Socrates)
- Thank you so much, this is gold !
NikolinoDE , VBA doesn't work with Excel Online
- You are right, I disregarded the labels 😞
