CELL function returns blank

[LisaGreen]

Hi,

I'm trying to use the CELL function to return the sheet name and it doesn't seem to work.

In experimenting if I use..
=CELL("filename",a1)
in A1 I don't see anything in the cell.
When I use F9 to evaluate it I see "".
Soooo I'm assuming a blank is returned.

The question is of course "Why" and what can I do to get the filename please?

TIA
Lisa

[HansV]

If you are working in a new, unsaved workbook, =CELL("filename",A1) will return an empty string.
But if the workbook has been stored to disk, you'll get something like

C:\Folder\Subfolder\[WorkbookName.xlsx]SheetName

[LisaGreen]

Hello Hans!

Saving seems to be part of the solution. Thank you.
However, the CELL function now returns the name of the last sheet I calculated for ALL sheets.

Is there a solution that you know of please?

TIA
Lisa

[HansV]

Specify the sheet as well as the cell:

=CELL("filename",Sheet1!A1)

If you rename the sheet, Excel will automatically update it in the formula.

[LisaGreen]

okay.. But the whole point is showing the sheet name. If I know it beforehand I may as well just type it in. Ahhh.. You are using the sheet ID I think which is different of course. I'll give that ago. Curiously this "problem" occurs across workbooks. Doing an F9 in one workbook and then changing to another workbook shows the sheet name there changing to the last one I calculated.

Maybe something for Alan to get his teeth into!!

As I said, I'll give your solution a go.. though there is a "sheet1" in both workbooks.

Lisa

[HansV]

The formula uses the tab name, not the "code name" of the sheet.
The difference between using the formula and typing the sheet name is that the formula will be updated automatically when you rename the sheet.

[LisaGreen]

Just re-read your post.. That works too!