LAMBDA Library: WorksheetName
A lambda to encapsulate the calculation of the name of the worksheet in which a referenced cell is located.
Development Rationale
The purpose in developing this lambda is to simplify the derivation of the worksheet name for a referenced cell. This can be done using normal formulas, but the lambda makes it a straight-forward task.
This lambda is volatile, since it depends upon the results of a CELL function to derive the location of a cell within a workbook and worksheet.
It cannot derive the name of a Chart sheet, only worksheets. It is not possible to reference a cell on a Chart sheet, therefore this technique does not work for them.
The process of deriving the worksheet name is based on calling the CELL function to return the filename, which is the full path and file name to the workbook and the worksheet name on which the targeted cell resides. The file name is wrapped in brackets. Using the TEXTAFTER function, it is possible to retrieve the worksheet name,
Defined Name Settings
To create the lambda, simply use the define name feature in Excel to define this name in the workbook where it will be used. The required settings are shown below. Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.
| Name:1 | cllib.WorksheetName |
| Scope: | Workbook |
| Comment: | A lambda that returns the name of a worksheet for a given targetCell. |
| Refers To: | =LAMBDA(targetCell,TEXTAFTER(CELL("filename",targetCell),"]",-1)) |
Syntax of Lambda
=cllib.WorksheetName(targetCell)
where:
targetCell is a reference to a range or a formula that returnes a reference to any single cell. Preferrably the cell is in the current workbook.
Sample File
An example file, showing the definition and use of this lambda is available here: cllib.WorksheetName.xlsx.
1 You may change the name of the lambda and drop the prefix. The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source. The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.