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.
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!
NuggetSA - nice simplification, 99 can be reduced to 32 as that is the maximum length of a sheet name. But this only works in the desktop version not the SharePoint or Web instance.
As mentioned before CELL("filename") doesn't work in Excel for web.
- Nor in Excel for SharePoint
To my knowledge there is no such app as Excel for SharePoint. Excel for Web is the common name for working with Excel file in browser, doesn't matter where such file is located - SharePoint, OneDrive or other supporting source.
