Your Sheet Names Are Killing My Formulas

Have you read all the Excel horror stories and costume ideas in the Very Scary Fall Giveaway for Excel Nerds? There’s some truly frightening stuff there! The entry deadline is tomorrow at noon, Eastern Daylight Time, so get moving if you haven’t entered already.

My Horror Story

One of my Excel horror stories involves sheet names. I set up a client’s workbook with pre-formatted data entry sheets, so sales managers could plan their annual product promotions. They would rename the sheets while working, to make it easier to navigate the completed workbook.

On a hidden summary sheet, I added formulas to calculate the sheet names. Then, INDIRECT formulas pulled data from specific cells on each sheet, and other formulas created grand totals. At the front of the workbook, the summary data was displayed in a monthly calendar, for sales managers to review. It was a work of art!

The Scary Phone Call

Everything worked well in testing, so we distributed the files to all the sales managers, and they started filling in their data. The next day, the phone rang – some of the workbooks were “broken.” Budget deadlines were looming, and the sales managers with broken files were in a panic. They sent me a couple of problem files, so I could figure out what was wrong.

On the Summary sheet, some of the formulas were working correctly, but others showed #REF! errors. Comparing the good and bad sheets, I couldn’t see any problems with the data that had been entered.

SheetNames03

Finally, after checking a few of the problem sheets, I spotted a similarity. All of them included an apostrophe in the sheet name! I removed the apostrophes, and the problem was solved. All the data showed up in the summary sheets, and the world was in harmony once again.

Sheet Naming Rules

I hadn’t anticipated that problem, since I never use apostrophes in sheet names. They’re valid characters for a sheet name, but maybe they shouldn’t be.

It’s hard to find the sheet naming rules in Excel’s help, but you may have seen an error message that lists them.

  1. The name can’t be more than 31 characters, and you can’t leave the sheet tab blank
  2. Only a few characters are invalid:

  : / ? * [ ]

SheetName02

Sheet Naming Suggestions

In addition to those rules, I have a couple of guidelines of my own.

  1. Use only letters, numbers and underscores in sheet names. Sometimes I have to use a space character, if a client requests specific sheet names, but I try to avoid it. For example, I’d use SalesData or Sales_Data, not Sales Data.
  2. Use different names for sheets and named ranges, to avoid confusion.

Your Sheet Naming Rules

  • What kind of names do you use for worksheets?
  • Any characters that you avoid or problems you’ve run into?

____________

You may also like...

9 Responses

  1. Kanti Chiba says:

    Dear Debra,
    Thabnks for a most interesting blog, I have learnt heaps from you.

    The formula to calculate the sheet names, was that an Excel formula or VBA. I know how to do it in VBA, but not with an Excel formula.

    Regards
    Kanti

  2. I’d suggest to avoid the exclamation mark in sheetnames too, formulas will become very confusing:

    =’Sheet1 !’!A2

    Note that for Excel XP and older you can even use the [ and ] in a sheetname, Excel 2003 and higher prevent this.

  3. KL says:

    Hi Debra,
    I myself don’t have any sheet-naming rules. That is probably due to the fact that I rarely use INDIRECT() and other volatile functions as a general rule. However, if I desperately needed to use INDIRECT(), I’d probably have as a rule something like this (assuming the sheet names are correct, say, coming from a defined name containg =GET.WORKBOOK(1+0*NOW())):

    =INDIRECT(“‘”&SUBSTITUTE(A1,”‘”,”””)&”‘!A1?)

    i.e.:
    1. duplicate any apostrophes in the sheet name
    2. add opening and closing apostrophes to the sheet name

    My 2 cents.

  4. Thanks Jan Karel and KL, those are great suggestions.

    Kanti, you can find formulas to calculate the sheet name on Chip Pearson’s Excel Formulas page. In the Miscellaneous section at the end, look under ‘Sheet Names’.

  5. Kanti Chiba says:

    Dear Debra,

    Thanks for pointing me to the Chip Pearson page. The Cell formula slipped my mind.

    regards

    kanti

  6. Bob Phillips says:

    Also, see http://tinyurl.com/yg7pkq5 for an Exel 2007 XML character horror story.

  7. Tim Buckingham says:

    Hi Debra,

    I use the same naming convention as yourself. Underscores where psaces necessary. I find that when setting up a contents page with links, the links don’t get along well with the spaces in the sheet names. Underscores are generally no prob!

  8. Bob, thanks, that’s another excuse for me to avoid coding the Ribbon!

    Tim, thanks for another reason to avoid spaces in sheet names.

  1. April 22, 2013

    […] Originally Posted by mchac Thanks Rick, But it's not a file name that i'm creating, it's a tab name. However I just tried to manually input a ":" in the tab name and it won't accept it so I suppose it's illegal there also. Sorry, I guess I read your message just a little too quickly. As for sheet names, this link might help you… Your Sheet Names Are Killing My Formulas | Contextures Blog […]

Leave a Reply

Your email address will not be published. Required fields are marked *