Automatically Updating Page Titles for Sheets

Has anyone ever asked you how to automatically link a cell to the title of a sheet?  For example, if the current worksheet is called "Sheet1," how do you link to this name such that a change in the name will be reflected in your cell link?  More importantly, isn't it amazing how often a senior person asks you to do something in Excel without even being sure of whether it's possible or how the hell it could work?

You might be in the process of creating a product model in which there are 25 different products, and each product occupies a sheet within the model.  Let's say that you name each sheet for each different product, and have a title on each sheet that also matches the name of the product.  If you decide to change the nomenclature, you may be making 25 changes twice over (or even more than that, if you are using the product name in multiple cells on each sheet).

How do we link to sheet names?

Obviously, the more you link things together, the less manual updating you will need to do.  So here is a way to link to a sheet name, for whatever purpose you might need (make sure your file is saved on the hard drive).

1. Start with a formula that will return a string of text containing the sheet name.  Assume you are on "Sheet1."  You can go to cell B3 and enter: =+CELL("filename",$B$3), which returns "C:\Folder\[filename.xlsx]Sheet1"
2. Having the filepath and sheet name, you can create a function that returns only the part of this string of text that follows the "]," which would be "Sheet1"
3. One function that will work to return only the text following "]" is the RIGHT function: =+RIGHT(CELL("filename",$B$3),a) where a is the number of characters following "]"
4. Obviously, if all of your sheet names are 6 characters in length, you can just use the function from step 3 with a = 6,.  But a truly dynamic function will require solving for "a" in all cases.  So first, let's assume a = b - c, where b is the total length of the filename and c is the total number of characters through "]"
5. To find "b," use =+LEN(CELL("filename",$B$3)), which tells you that the filename is 31 characters in length
6. To find "c," use =+SEARCH("]",CELL("filename",$B$3),1), which tells you that "]" occupies the 25th position starting from the left in "C:\Folder\[filename.xlsx]Sheet1"
7. Using a = b - c, we have a = 31 - 26 = 5.  In formulaic terms, a = LEN(CELL("filename",$B$3))-SEARCH("]",CELL("filename",$B$3),1)
8. Thus, using the formulae from steps 3 and 7, the sheet name is =+RIGHT(CELL("filename",$B$3),LEN(CELL("filename",$B$3))-SEARCH("]",CELL("filename",$B$3),1))

Holy sheet!

-F-One

My Favorite Cousin: The F5 Key

 Keep going...keep going

Everyone knows that the fastest way to navigate to a random cell is entering the cell reference into the "Go To" menu.  In other words, if you want to get to cell AZ355, type: F5, AZ355, ENTER

Similarly, you know that if you have named a cell or cell range, you can select that name on this menu, and Excel will highlight the selection for you.

But what happens if you want to select some other group of cells that isn't named and isn't contiguous?  What if you're a first-year analyst who is being forced to color all of the hard inputs in a model to blue?  What if the model is 100 pages?  What if the penalty for forgetting to color one of the numbers is being called an idiot even though you sit next to an admin from Brooklyn that doesn't know how to read?

There is a solution for everything in Excel, and in this particular situation, it would be: F5, ALT + S, which brings you to the "Go To Special" menu.

...there!

At this menu, you can select "Constants" (ALT + O, ENTER), and then uncheck all of the boxes except "Numbers."  Now, you can highlight only the hard inputs in your model, and turn all of those numbers blue with just a couple of keystrokes, rather than selecting range after unbearable range and hoping you didn't miss anything along the way.

Step 1: Select a range

Step 2: Select criteria

Step 3: Only format these numbers...

Think of some of the other possibilities!  If you have hidden or grouped data that you do not want to format, you can select only visible cells and format those.  If you want to find out what cells are conditionally formatted, rather than trying to guess, you can do that easily.  In any case, my cousin F5 is extremely underutilized for how powerful it is.

Comments/questions welcome.

-F-One

Greetings!

Welcome!  Many (3) people have asked me to share my perceived wealth of excel knowledge with the rest of the world, so I have created this blog with the plan of regularly posting facts, "how-tos" and other updates on Excel for financial modeling and corporate finance in general.

What qualifications suggest that I can teach you, the reader, anything useful about Excel?  Well, I graduated from college (barely) in 2006 having taken one semester of an Excel class (B-), worked 3 years at a large investment bank, and currently work at a private equity firm.  So I have no legitimate credentials to support my case, but you can be the judges of whether my contributions are useful.  Here are some brief notes on the intended focus of this blog:

1. Formulas that improve the efficiency of financial modeling, data analyses and other commonly needed functions within Excel
2. Commands that improve efficiency, formatting and accessibility within Excel
3. I am currently using Excel 2007
4. Topics I do not intend to cover in-depth are: visual basic (not my expertise), statistics (my worst class in college), and pivot tables

The key word is efficiency.  Everywhere I have looked, there is someone using Excel less efficiently than they could, and in turn being less productive for no good reason.

As mentioned, the Excel information on this blog will be most relevant to corporate finance, particularly those people that work in investment banking (and related areas) and frequently use Excel.  But hopefully anyone looking to save some time or learn some interesting things about Excel will benefit.

If you need Excel help or have a topic you want to learn more about, feel free to leave a comment!

Thanks so much,

Mr. F-One