workbook and sheet name via formula

Guy_Boswell 

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)