excel get sheet name from address

Question

i am trying to get the full sheetname dynamically from the address with the below formula in another sheet. is there a better way ?

address : '[Airport - Daily Burn Rate_2024.05.08.xlsx]HQ 2024'!$A$1

result : 'HQ 2024'

my formula :

="'"&MID(TEXTAFTER(CELL("address";'HQ 2024'!A1);"]";1;0);1;
               FIND("~";SUBSTITUTE(TEXTAFTER(CELL("address";'HQ 2024'!A1);"]";1;0);"'";"~";1))
               )

PS if the sheename changes, i dont want my other formula connected to this sheet to be ruined...

Answer 1

Here is an alternative way, one could try using TEXTBEFORE() and TEXTAFTER():

---

="'"&TEXTBEFORE(TEXTAFTER(CELL("address",'HQ 2024'!A1),"]"),"!")

Answer 2

Why don't you use Formula Evaluation, which can be found in ribbon "Formulas", chapter "Formula Auditing", "Evaluate Formula", like in the following screenshot:

By evaluating step by step, you can see where it goes wrong.

Here, e.g. you can see that the problem is caused by the CELL("address";'HQ 2024'!A1) function, which returns the address of your cell, but not the content.

Answer 3

I use:

This in cell DA1 to get the sheet name:

MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,50)

uses the position of the "]", and then:

This to get a list of all the sheets between the firstone and lastone, as there are other sheets I do not want listed:

TEXTSPLIT(TEXTJOIN(",", FALSE,Firstsheet:Lastsheet!DA1),",")