# SHEETNAME  	SHEETNAME returns the parent worksheet name of the reference cell.



## smozgur (Dec 8, 2020)

SHEETNAME function returns the parent worksheet name of the reference cell.


```
=LAMBDA(reference,
    LET(name, CELL("filename",reference),
        RIGHT(name, LEN(name) - FIND("]", name))
    )
)
```
Sample.xlsmAB1Current worksheetOther worksheet2Sheet1Sheet2Sheet1Cell FormulasRangeFormulaA2A2=SHEETNAME(A1)B2B2=SHEETNAME(Sheet2!A1)


----------



## McKay_S (Nov 24, 2022)

Does anyone have a lambda that can provide a list of all sheetnames (without the usual VBA route)? I'm stumped on this one.


----------



## jdellasala (Dec 5, 2022)

McKay_S said:


> Does anyone have a lambda that can provide a list of all sheetnames (without the usual VBA route)? I'm stumped on this one.


Not really. I have a Named Formula / Function that can retrieve all sheet names in a single spilled columnar array:

```
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())
```
The REPLACE portion of that formula gets rid of the Workbook name. The T(NOW()) forces it to refresh, otherwise you have to force a refresh to get new/renamed sheets. Just wrap it in TRANSPOSE to get the sheets in a list.

As for the Macro part, *GET.WORKBOOK(1)* is an old Macro 4 function that's still in Excel. If you use this in a new file, Excel (365) will automatically save it as a Macro Enable Workbook (.xlsm). Obviously if you incorporate it into an existing Workbook, it will have to be Saved As a Macro Enabled Workbook.

When I first started working with this, Excel was blocking the functionality. I found the fix to it on the Microsoft site and posted it *here*.


----------

