Filename formula not working

[StephenBart]

I am running office 365 on my desktop.

I have a template with a cell B4 with the formula = Cell("Filename", A1).

When I create a worksheet from this template the formula in Cell B4 returns #value error

If I save the workbooks as book1.xls the error persists.

If I close the now saved work book when I reopen the book cell B4 returns the value Book1.xls as expected.

However if I then save as Book2.xls the value in the cell B4 is still Book1.xls - I have to close and reopen Book2.xls to update this cell.

Its like the formula is returning the filename when the workbook was last closed, not last saved (i.e., not the current filename)?

Does anyone know a way to have a cell display the current filename with out having to close and reopen the workbook?

Thank you

 

[Alex Blakenburg]

You don't need to close and reopen the workbook but you do need to do something in the spreadsheet that triggers it to recalculate.
You can:
• Press F9 which runs recalculate
• Enter / change something in any cell
• Put a recalculate command in the workbook module which triggers when the spreadsheet is saved but this will mean you need to make the spreadsheet macro enables (xlsm or xlsb).

 

[StephenBart]

Thank you - yes that worked perfectly.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.