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.

Who knew?

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.