Issue with File Name changing in cell
 

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?

Issue with File Name changing in cell
 

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?

Issue with File Name changing in cell
 

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?

Issue with File Name changing in cell
 

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?

Issue with File Name changing in cell
 

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

Issue with File Name changing in cell
 

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

Issue with File Name changing in cell
 

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

Issue with File Name changing in cell
 

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