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?

Calculations are probably set to Manual.

Tools|Options|Calculation|Automatic

HTH,
Paul

--

"JDaywalt" wrote in message
...
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?

No they are not set to manual. Any other thoughts?

"PCLIVE" wrote:

Calculations are probably set to Manual.

Tools|Options|Calculation|Automatic

HTH,
Paul

--

"JDaywalt" wrote in message
...
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?

Micorsoft says that the Cell function is not volatile but it really is...
Check out this link...

http://www.decisionmodels.com/calcsecretsi.htm

That being said even if it is voaltile, as you have indicated that it is, it
still needs a calculation to run before it will recalc. SaveAs does not
trigger a Calc to run so yes you will need to hit F9 or just wait until the
next time a calculation runs...
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

No they are not set to manual. Any other thoughts?

"PCLIVE" wrote:

Calculations are probably set to Manual.

Tools|Options|Calculation|Automatic

HTH,
Paul

--

"JDaywalt" wrote in message
...
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?

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

I made the adjustment just as you indicated -- the formula is actually in
cell A1, and I specified this same cell (A1) in all of my functions calls but
still have the same issue & must recalculate to return the correct value.
Should I assume it is a "volatile function" as Jim suggested?



"Dave Peterson" wrote:

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

Is calculation set to automatic?

What formula did you use--maybe you missed one???

JDaywalt wrote:

I made the adjustment just as you indicated -- the formula is actually in
cell A1, and I specified this same cell (A1) in all of my functions calls but
still have the same issue & must recalculate to return the correct value.
Should I assume it is a "volatile function" as Jim suggested?

"Dave Peterson" wrote:

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


--

Dave Peterson

I was mistaken on the Save As not forcing a calc. It does force a calc so
long as either your calcualtion is set to Automatic or you have checked off
Calculate on Save (in your calculation options), so all volatile functions
should be evaluated.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I made the adjustment just as you indicated -- the formula is actually in
cell A1, and I specified this same cell (A1) in all of my functions calls but
still have the same issue & must recalculate to return the correct value.
Should I assume it is a "volatile function" as Jim suggested?



"Dave Peterson" wrote:

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