Some businesses use Microsoft® Excel® to prepare and present their business reports. Report headings often contain worksheet names, and to make your reports more efficient, you can link these worksheet names to a specific cell. So, instead of retyping the worksheet names in your report headings, you can easily apply the MID and FIND functions in Excel. This will save you the time and effort, as well as eliminate the risk of unnecessary errors.
Using the steps below you can have your worksheet names for each month in a workbook and display the worksheet names in the report heading automatically.
Note: You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® 2010 and 2013
1. Select cell C1. Then select the Formulas
2. Select Text and then MID from the text functions list.
3. Place the cursor in the Text box and enter CELL(“filename”,A1).
The CELL function returns information about the formatting, location, or contents of a cell.
- It has two arguments info_type and reference.
- Info_type: text value that specifies the information type to be returned.
- Reference: Is a cell that you want information about.
- “Filename” will return the path of the filename.
C:\Excel Tips\[Linking a worksheet name to a cell.xlsm]Janaury
4. Place the cursor in the Start_num box and enter FIND(“]”,CELL(“filename”,A1))+1.
- The location within the file path name from where to start extracting the worksheet name.
- “]” instructs Microsoft® Excel® to find the position for the square bracket.
- +1 ,adds 1 to the position returned by searching for the square bracket.
5. Place the cursor in the Num_Chars box and enter 32, then click OK
- 32 is the maximum number of characters for a worksheet name.
The worksheet name will automatically be linked to the cell, thus saving you time and effort. In addition to this nifty trick, whenever you decide to change your worksheet’s name, it will auto-update to the cell it was linked too.
