Use the Cell() Function. The CELL function returns information about the formatting, location, or contents of a cell. It has the following syntax:

 CELL(info_type, [reference])

The first argument is an attribute and the second argument is a cell reference.  There are quite a few info-types that you can look up online but one you can use for this is the “filename” attribute.  It will return the full file name with the path along with the sheet name.  You can extract just the sheet name.  Just copy the formula at the bottom.

The first formula (in cell B4) returns the name of the file that cell A1 lives in.  You could have used any cell in the file to return the same value, but A1 works.

=CELL(“filename”,A1)

Since all you really want is the sheet name (which you can see from the bottom is “Months”) copy and paste the following formula (in cell B4 of the example) to extract the sheet name from the rest of the formula.

 =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255)