If you have a multi-sheet workbook in Excel 2010 then it might be useful to learn how to capture worksheet names in a cell. This has a range of practical uses from report header management through to building sheet related formulas. Read on to learn this effective technique for linking sheet names to a cell in Excel 2010…
Multiple Worksheet Example
Taking a basic example we have a sales workbook with monthly sales on each worksheet. Each worksheet is labelled with the month and year of sales, Jan-14, Feb-14, Mar-14 and so on:
The aim of this exercise is to get the worksheet name populated in Cell A1 of every sheet to produce a neatly linked header. In the long-run this will be one less thing to change if the file is carried on into next year as there will be no need to change headings and worksheet names, we can just change the worksheet name.
To come up with a solution there are three key Excel statements that will help:
1) The FILENAME statement
2) The MID statement
3) The FIND statement
The FILENAME statement
If you type =CELL(“Filename”) into any cell of a saved workbook then it will return the full path, file name and current sheet name back into that cell.
For example if typed into Cell A1 of the example workbook then we get the following:
As you can see from the above image the formula =CELL(“FILENAME”) returns the entire path as the text string “C:\My Docs\[Sales Data.xlsx]Jan-14”.
Importantly if we type the formula onto the “Feb-14” worksheet then it would return “C:\My Docs\[Sales Data.xlsx]Feb-14” and likewise for all the other worksheets.
The format of the text string is always the same with the filepath followed by the filename, which is encased in square brackets, followed lastly by the worksheet name. This reason this is important to note is because it means as long as we always pick up the text after the closing square bracket we will only be capturing the worksheet name, and that is the aim.
The MID statement
The MID statement is an effective way of cutting out a portion of text from a text string. We can use this to tell Excel what part of a text string we want to keep making it perfect for including in the solution to this problem.
The MID statement takes the format:
=MID(text, start_num, num_chars)
This is where text refers to your cell or text string, start_num is the position you want to start counting from and num_chars is the number of characters you want to include.
So with the MID statement and the Filename statement we are nearly there but one last mention is needed and that is the FIND statement.
The FIND Statement
The FIND statement locates a character in a text string and tells us what position it is in. In this example we have a closing square bracket which we have identified is our key character, we want to make sure the MID statement takes everything after that closing square bracket.
The FIND statement takes the format:
This is where find_text refers to the character (or sequence of characters) that you want to find and within_text is where Excel needs to look for it, i.e. the cell reference or text string.
We now have all the pieces for a solution:
1) We can get the worksheet name using the FILENAME statement.
2) We know how to chop out text from one position to another in a text string using the MID statement.
3) We know how to tell Excel to find a specific character in a text string, i.e. how to find the closing square bracket, using the FIND statement.
Putting it all together and you are left with the final solution:
This returns the result:
You can simply copy that formula into every worksheet and it will return that worksheet name in cell A1 (if that’s where you put the formula!)
This solution is good to go so if you want to test it just copy and paste it directly into one of your saved workbooks today.