• Excel Book Excerpt

Excel Add the Worksheet Name as a Title

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Add the Worksheet Name as a Title

Problem: I have 12 worksheets, labeled January through December. Is there a formula that will put a worksheet name in a cell?

Strategy: You can parse the sheet name from the CELL function.

The CELL function can return a variety of information about the top-left cell in a reference. =CELL(“Col",A1) will tell you that A1 is in column 1. For this particular problem, =CELL(“FileName",A1) will return the path, filename, and worksheet name of a saved workbook, as shown in cell A1 below.

LE10000231.jpg 

Figure 259 CELL returns the path, filename and worksheet name.

To isolate the sheet name, you look for the right square bracket by using the FIND function. Then you use that location plus 1 as the start position for the MID function.

­=MID(CELL(“FileName",A1),FIND(“]",CELL(“FileName",A1)+1,25)

returns the worksheet name. Note that the final 25 argument is any number large enough to handle the longest sheet name you’ve used.

Additional Details: If you need to insert just the worksheet path in a cell, you can use =INFO(“Directory") instead of trying to parse it from the CELL function.

Gotcha: The INFO function used to be able to return several bits of information about memory available, total memory, and so on. While these arguments were accepted in Excel 2003, they were never correct. In Excel 2007, Excel will return #N/A if you use the INFO function to return available memory.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: