SHEETNAME

[smozgur]

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.xlsm
	A	B
1	Current worksheet	Other worksheet
2	Sheet1	Sheet2
Sheet1

Cell Formulas
Range	Formula
A2	A2	=SHEETNAME(A1)
B2	B2	=SHEETNAME(Sheet2!A1)

 

[McKay_S]

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]

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.