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.
With Excel 365, this can be beautified by creating a defined name in workbook scope:
Name:
GetSheetName
Refers to:
=LAMBDA(refCell; LET(frmTxt; FORMULATEXT( INDIRECT( ADDRESS(ROW();COLUMN()) ) ); SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt;"!"); "("; -1); "'"; "") ))
Then, you can use a simplified form in your worksheets:
=GetSheetName(Sheet1!A1)
Again, you have to add the name of the sheet in the cell reference manually if you want to retrieve the name of the same worksheet. After that, it is automatically updated if you rename or duplicate the worksheet.
GeertCL that only returns 0
Hi Guy,
There must be something specific about my Excel settings that differs in your case.
As you can see in the above screenshot, I have the expected results on the web browser-based version of Excel Online, both for the name of the same worksheet as for another worksheet.
However, I did notice that one cannot create the defined name in the web browser, only use it. You have to open it with the Excel app to have access to the Name Manager.
Also, in my localisation, the separator is a semi-colon, not a comma.
Unfortunately, some testing and debugging will be necessary in your case.
Row 4 shows how you can use the LAMBDA function directly, without defined name: note that you have to add the ('Sheet1 Copy'!A1) argument at the end.
=LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!"), "(", -1), "'", "") ))('Sheet1 Copy'!A1)As the result shows, this is fooled by the preceding exclamation mark that appears in the TEXTBEFORE function.
For testing, one can adapt the TEXTBEFORE function to search for the exclamation mark in reverse direction with the -1 value in the third argument (row 5):
=LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!",-1), "(", -1), "'", "") ))('Sheet1 Copy'!A1)GeertCL Thanks for this pointer. It works fine for me except when I try to use it twice in the same formula. For example, if my cell contains:
="Refer to sheets " & GetSheetName(Sheet1!A1) & " and " & GetSheetName(Sheet2!A1) & " for more information."The result is:
Refer to sheets Sheet1 and Sheet1 for more information.
When I put the following formula in a cell:
=LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!",-1), "(", -1), "'", "") ))('Sheet1'!A1) & " " & LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!",-1), "(", -1), "'", "") ))('Sheet2'!A1)The result is:
Sheet2 Sheet2
I'm not really sure what's happening differently, so that in one case it's the first sheet name that's repeated, while in the other case it's the second sheet name. ...
I also created a second formula (based on yours) to get the cell reference:
=LAMBDA(refCell,LET(frmTxt,ADDRESS(ROW(refCell),COLUMN(refCell)),SUBSTITUTE(frmTxt,"$","")))If I leave ROW() and COLUMN() empty, it only returns the address of the cell in which the formula is placed, but it works if I put in 'refCell'.
Interestingly, when I use this formula multiple times in a cell, each reference displays correctly, without duplicates.
Any idea how I could fix this issue with the sheet name lambda? (I'm working in the desktop version now in order to use the name manager.)
Thanks!
In its current form, GetSheetName is a narcist formula. It just looks at the text of the formula in its own cell, and extracts the text between the first exclamation mark and the opening parenthesis before it, eliminating eventual quote characters. It is not actually aware of the cell reference that you provide as an argument, and never considers the second occurrence of GetSheetName.
In a reference to another worksheet, one can use the CELL("ADDRESS",refCell) function. But if the referenced cell resides on the same sheet, the name of the sheet is not returned and one has to go back to the formula text.You could create a custom function "GetOtherSheetName" as
=LAMBDA(refCell;SUBSTITUTE(TEXTAFTER(TEXTBEFORE(CELL("ADDRESS";refCell);"!");"]");"'";""))It will just not work if you reference a cell in the same worksheet.
As a comment, you may install Advanced Formula Environment (AFE) on Excel for web, practically no need in Name Manager then.
