Wednesday, March 16, 2011
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:
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.