CELL("filename") returns the same sheet name on three different sheets

See attached. The following formula is found in the same cell in each of three worksheets:

=CELL("filename")

When I entered it on Sheet3, it showed that the sheet name was Sheet3, but when I returned back to Sheet1, it also said Sheet3. When I hit F9, it showed Sheet1, but then so did the other two sheets!

I discovered this odd behavior attempting to help in another thread.

I have done a bit of web searching but find no mention of this, only a description of how the CELL function works.

If you omit a cell reference the formula will refer to the sheet of the last cell that you changed. Put in a cell reference (any cell) like this

=CELL("filename",A1)

....and it will always refer to the sheet in which the formula resides

Excel help for CELL says

"reference Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range."

You have to include a cell reference that will "anchor" the formula to the specific sheet:

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

The cell reference can be to any cell. The formula is referencing cell A1 but it doesn't need to be entered in cell A1.

This was solved by Tony Valko in the other thread. The problem does not occur if you include the optional cell reference argument:

=CELL("filename",A1)

Originally Posted by daddylonglegs

Excel help for CELL says

"reference Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range."

In other words...

If a cell reference is omitted, the formula will return the sheet name of the sheet that was active when the last calculation took place.

Agree entirely Biff - of course the help text is generic because it refers to any info_type, e.g. if you use

=CELL("format")

then, similarly, you get the format of a variable cell rather than a fixed one