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.
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.
GeertCL Thanks so much for the reply. For reference, my workbook is saved in Teams, and I have it open both online and in the desktop app simultaneously (so I can see how the changes I make in the desktop version work – or don't work – online).
So, I understand that the cell reference of the refCell input isn't actually doing anything (in the GetSheetName function); it's basically just there because there needs to be something to anchor the reference. What I don't understand, though, is why a second use of the function in the same formula doesn't consider the 'new' refCell input. Shouldn't it evaluate the second function 'independently' of the first? Why would it return the same value for both?
For example, in a sheet called 'Info', I put the following formulae in cells B1, B2, and B3 respectively:
=GetSheetName('Scores Q1'!A1) =GetSheetName('Scores Q2'!A1) =B1 & B2and it returns:
Scores Q1
Scores Q2
Scores Q1Scores Q2
just as expected.But when I put this in cell B4:
=GetSheetName('Scores Q1'!A1) & GetSheetName('Scores Q2'!A1)it returns:
Scores Q1Scores Q1
So it's not clear to me why it returns the first refCell both times, rather than evaluating each function in turn, independently.
Unfortunately, because I'm working with a shared file, I need everything to be able to work both in the desktop app and the online version of Excel. The output of the 'GetSheetName' function is the same online as in the desktop app (I just can't call the function in a new formula in the web app). But the CELL() function breaks: for example, in the desktop app, I put =CELL("address") in M9, and it returns "$M$9", as expected; but then in the web version, the same cell shows "$J$10". I haven't tried putting CELL() in the named lambda function, but I imagine the same thing will happen, so I'd best avoid it.
Thanks again for your help – I appreciate it!
Suggested function is based on the text of the formula in the current cell, in second case it takes text
"=GetSheetName('Scores Q1'!A1) & GetSheetName('Scores Q2'!A1)"
and extracts the text before first ! and next after first (. Result is Scores Q1
Everything after first ! is ignored.
Something like
GetSheetNameNew = LAMBDA(refCell, LET( frmTxt, FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))), CONCAT( DROP( TEXTSPLIT( TEXTJOIN(";", , DROP(TEXTSPLIT(frmTxt, "'!"), , -1)), "('", ";" ), , 1 ) ) ) );could work.
And CELL("filename") doesn't work in Excel for web, doesn't matter is it wrapped by lambda or not.