Getting past the 31 characters limit for worksheet names

The Problem

As anyone who would read this blog would know by now, much of the content is driven by questions forwarded by Allen Wyatt at http://excelribbon.tips.net/.  

A recent question regarded how to get around the length limit on worksheet names.  Below is my answer to him.  His published answer is here. (As a note, what he published is, I think simpler and more direct whereas this one stretches the question.)

What Won't Work

First, my research indicates Microsoft has a hard limit of 31 on the number of characters for the Worksheet Name property.  

Restating the Problem

My experience is that when I need very long names for worksheets it is often driven by two requirements: 

• Users need to quickly know which worksheet to open.
• The workbook is an array of similar worksheets for different entities e.g. Each Department has its own set of worksheets, e.g. Performance Metrics, Staffing by Month/Year, Budget etc., for multiple years---- or ----
• The worksheet names are the names of people, which can easily be more than 31 characters, and the first set of recommendations won’t work.

If the first case, here are some things I've used to make it easier for people to find the worksheet they need:

1.     Organize the worksheets in a specific order, e.g. Budget, then Staffing, then Expenses for each department of All Budget worksheets together but the Departments in a specified order..

2.     Use common but standard abbreviations.  For example, "Mfg" for "Manufacturing" eliminates 10 characters, “Bdgt” for “Budget” saves 2, etc. 

(See Suggestions for Shortening Words, below)

3.     Name consistently.  For example, Sls Bdgt and Mfg Bdgt instead of Sls Bdgt and Bdgt – Mfg. This makes it easier for people to scan across.

4.     If there are three years of worksheets for the current, most recent, and next year, use CY, LY, & FY or C, C-1, C+1 instead 2014, 2013, 2015 respectively.

5.     Use the Tab.Color property to visually differentiate certain types of worksheets or groups.  This not only provides the information needed but people can learn quickly to focus on the color to find their information.  For example:

• All worksheets of a department could be one color.
• HR (Staffing Sheets) with a Blue tab, Budget in Green, and Expenses, Red.

The above assumes all worksheets must be visible at all times.  An alternative would be to have a form or set of worksheet controls to controls which worksheets Hidden/Unhidden, e.g. use a drop-down  to select which department’s worksheets are shown or a set of check boxes to select the types of worksheets to show.

If the long name is absolutely essential, i.e. if user community refuses to use the abbreviations or dealing with peoples’ names (See Suggestions for Shortening Words, below), a third option would be to put the long names in a worksheet in a column next to the names on the tabs, then using an Event procedure such as this to switch to the worksheet. 

Manufacturing Budget  2014	Mfg Bgt 2014
Manufacturing Expenses January 2014	Mfg Exp 2014-01
Manufacturing Expenses February 2014	Mfg Exp 2014-02
Manufacturing Expenses March 2015	Mfg Exp 2014-03
Manufacturing Expenses April 2016	Mfg Exp 2014-04
Sales Budget  2014	Sls Bgt 2014
Sales Expenses January 2014	Sls Exp 2014-01
Sales Expenses February 2014	Sls Exp 2014-02
Sales Expenses March 2015	Sls Exp 2014-03
Sales Expenses April 2016	Sls Exp 2014-04

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next

    Application.Goto Reference:=ThisWorkbook.Worksheets(Target.Offset(0, 1).Value).Range("A1")

    On Error GoTo 0

End Sub

This method would allow the users to become accustomed to the abbreviations being used at their own speed.

Suggestions for Shortening Words

• Eliminate vowels except when the vowel is at the end of a word/name, then add back in from the front if needed:  Macaroni -> Mcrni -> Macrni) This has its challenges in some languages, such as Hawaiian where consonants and never sequential and Eastern European languages where vowels can be as rare
• Make a double consonant into one. Spaghetti -> Spghtti)
• When using abbreviation, minimize the range for the length. As a general rule, aim for 3: "Sls", "Mfg" .  This makes scanning through simpler for the user.
• Use commonly capitalized abbreviations, e.g. HR for Human Resources.
• Eliminate spaces and use PrprCptlztn…er, Proper Capitalization