Use:

Cell("filename",a1)
(in all your function calls.)

If you don't specify a cell, then excel will use the workbook/worksheet that was
active when excel recalculated.

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

===
You don't have to use A1. In fact, I'd use the cell that held the formula.
Then I wouldn't have to worry about column A or Row 1 being deleted--which would
screw up my formula.



JDaywalt wrote:

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?

--

Dave Peterson