Excel 2020: Put the Worksheet Name in a Cell
January 29, 2020 - by Bill Jelen

If you want each report to have the name of the worksheet as a title, use the following formula:
=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)) &" Report"
The CELL() function in this case returns the full path\[File Name]SheetName. By looking for the closing square bracket, you can figure out where the sheet name occurs.

Title Photo: Jannes Glas at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.