You might want to check your formula. Put it in a cell in Sheet1 to return Sheet1. Then put it in a cell in Sheet2 to return Sheet2. Then go back and look at the cell in Sheet1. Repeat for Sheet3.
If it works like it's working in my workbook, all cells with the formula will show the name of the last sheet it was executed in.
The formula which will return the name of the sheet in which it resides is:
BTW...both of the formulas will return #VALUE until the workbook is saved and the sheet recalculates.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.