I have used this commonly recommended formula to populate the file name
within cell A1 of my worksheet:

=MID(CELL("Filename"),FIND("[",CELL("Filename"),1)+1,(FIND(".",CELL("Filename") ,1)+4)-(FIND("[",CELL("Filename"),1)+1))

I am experiencing a couple of issues with this:

1. If I do a File...SaveAs and give the file a new name, the contents of
cell A1 does not automatically update (I have to hit F9 to recalculate
correct name).

2. If I open a separate workbook, the file name in cell A1 changes to
reflect the file I just opened. (Again, I have to hit F9 to recalculate
correct name).

Is there an adjustment I can make to remedy this problem? Or do I just have
to hit F9 each time?