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.
- 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. These results have not been correct since Windows XP. Today, Excel will return #N/A if you use the INFO function to return available memory.