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.
About the Lambda Calculations
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 name1 feature in Excel to define this named lambda in the workbook where it will be used. The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.
| Name:2 | λWorksheetName |
| Scope: | Workbook |
| Comment: | A lambda that returns the name of a worksheet for a given target range. |
| Refers To: | =LAMBDA(target_range,TEXTAFTER(CELL("filename",target_range),"]",-1)) |
Syntax of λWorksheetName
=λWorksheetName(target_range)
where:
target_range is a reference to a range or a formula that returns a reference to any single cell. Preferably the cell is in the current workbook.
Sample File
An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.
Version History
| Version | Release Date | Release Notes |
| 1.0 | 16/02/2024 | Initial release named cllib.WorksheetName |
| 1.1 | 31/08/2025 | Renamed as .λWorksheetName as part of reorganisation of the λ Library 2.0. Modified argument names to match standard Excel lower case with underscore. |
1 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.
2 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.