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.
Actually, one can do it without VBA or hidden sheets using the following formula:
=IF(FALSE;'Sheet1'!A1;MID(FORMULATEXT(INDIRECT(ADDRESS(ROW();COLUMN())));11;FIND("!";FORMULATEXT(INDIRECT(ADDRESS(ROW();COLUMN()))))-11)) In this formula, you replace the 'Sheet1'!A1 by the reference to the first cell in the sheet whose name you want to show. It can be the current sheet, but then you have to add the sheet name once, manually. If you are lazy like me, you can also enter this formula temporarily on another sheet, use point-and-click to the current sheet to insert the reference, and then copy the formula over.
The formula exploits the fact that it uses its own text, and that the prefix is known ("=IF(FALSE;" - 11 characters). That is where the ADDRESS/ROW/COLUMN construct comes in: that is how the FORMULATEXT gets the formula of the current cell, while referencing another cell.
If you rename the sheet, or copy it as a duplicate sheet, the reference updates automatically.
This is the basic version of the formula. The result will still contain the single quotes if the sheet name contains blanks. You can surround the formula with a SUBSTITUTE(formula;"'";"") construct to get rid of those.
In Excel 365, this can be simplified with the TEXTBEFORE and LET functions, and you can create a GetSheetName(reference) function-like defined name that uses the LAMBDA dynamic function.
