Dynamic sheet name in Excel

Question

I can reference a sheet name from another sheet

='Sheet1'!A1

How can I do thus with formula (not VBA or Python, sadly) to find out the name of the sheet dynamically (ie without knowing the name sheet)? Just to be clear I'm after the sheet names/ tab names at the bottom of the window, not the worksheet name

I've got number of spread sheets with a number of sheets (all named differently) and I was looking for a formula that will work with all. One size fits all , if you will.

Answer 1

Try this:

In the Name Manager create a Name using this formula:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Then in your worksheet, use INDEX function to retrieve sheet names like this:

=INDEX(SheetList,1)

To List all sheets then select the target range and enter SheetList as array formula using Ctrl+Shift+Enter.

{=SheetList}