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),"]")
- Brilliant - even shorter, thank you!
Introduced on 19 October 2022, O365 only. So keep in mind which version you have 🙂
