Issue with extracting a sheet name in Excel

Single sheet file issue

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.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.