Formula to get sheet name

[dluhut]

Hello

Assume I have the following sheets

'Sheet1', 'Start', '100', '101', '102', 'End', 'Something', 'AnotherSheet'.

Now in Sheet1 from A1 to Ax, is there a formula that can give me the sheet name from the sheet range of 'Start' to 'End'?

In the above example, I'd like to have cell
A1 to return '100'
A2 to return '101'
A3 to return '102'

And if a user move sheet 'Something' in between '101' and '102', then my formula would automatically update the value to be
A1 to return '100'
A2 to return '101'
A3 to return 'Something'
A4 to return '102'

[AlKey]

You would need a VBA code for this
1. Copy code below
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.

Use formula:
Enter in A1 and copy down

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[FDibbins]

Another way to do this, is to use a UDF (which is still essentially a macro)

1st create a range name (I called mine Sheetnanes
Then put this in the Refers To box: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Note that this uses a UDF (User Defined Function) and requires that you save the file a .xlsm