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.
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- Bloggs20: Brilliant solution, thank you so much. Replace the ; with a , and it works
I modified it to this to the name without the quotes:
B1='Sheet 1'!A1 format as 0;0;
C1=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(B1), "!"), "'", "" ), "=", "")
And boom, works
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 !
