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.
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.
- The name can't be more than 31 characters, and you can't leave the sheet tab blank
- Only a few characters are invalid:
: / ? * [ ]
Sheet Naming Suggestions
In addition to those rules, I have a couple of guidelines of my own.
- 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.
- 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?