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.
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 - Thank you! I had to replace the semicolons with commas for it work on the desktop and online versions, but besides that this is a good workaround
You may sync online regional settings with your local one in File->Options->Regional Format Settings
