I wrote about a formula to extract the sheet name many years ago, but I just found an issue with it. Click here to see older blog post.
I just found out that the formula can be broken in a certain situation.
It won’t work if
- the sheet name and the file name are the same AND
- there is only one sheet in the file
In all cases below the file name is Test_sheet_name.
In this case the sheet name is Test and all is ok.
In this case the Sheet name is Test_sheet_name, the same as the file. Also only one sheet.
As you can see the Cell function in A1 only returns the file name – it omits the sheet name.
If you add a space to the end of the sheet name its ok – see below.
So it is an unusual situation (but it did happen to me while I was creating a macro) so beware of the dreaded file sheet = name issue.
Adding another sheet fixes the issue too.