Reference Please...

There are times when it is good to be able to automatically reference the Name of your Excel workbook or worksheet that you are doing your report on. If you wish to have the results in a cell refer to the Entire Name and Location of your worksheet and workbook, that is easy: You merely put CELL(“filename”) in any cell, and you get the entire kit and caboodle such as the following (yours may even be longer):

C:\My Documents\ExcelBlog2011\[Sales Report.xlsx]Sales 2011

But what if you want to just to have your cell reference the Name of the Worksheet you are on? This can be very useful when printing reports, and can be accomplished using the following formula:

=MID(CELL("filename", A1),FIND("]",CELL("filename",A1),1)+1,254)

Very nice, you say, but how does it work? Here it is broken down starting with the FIND function in the middle of the equation:

1. In this instance, FIND looks for the Right Square Bracket in the resulting CELL(“filename”, A1) string (please note that the cell reference “A1” is entirely arbitrary, and any cell reference will do).
2. MID looks in the same resulting CELL(“filename”, A1) string and uses the Start Number that was found in Step #1 above and Adds 1 (so you do not get the Bracket in your result).
3. The final argument, 254, merely allows up to 254 characters to be returned.

The final result is the Name of the Worksheet you are on, or in this case, Sales 2011.

Powerful Stuff! Copy the formula and paste it into your own worksheet to see how Cool this works.

Cheers!