Forum Discussion
Solved
workbook and sheet name via formula
I have a standard header I put on almost every excel workbook I create I have been using for years. It includes several standard items, including Workbook name and Worksheet name, which are extracted...
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.
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
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
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