Issue with File Name changing in cell

J

JDaywalt

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?
 
P

PCLIVE

Calculations are probably set to Manual.

Tools|Options|Calculation|Automatic

HTH,
Paul
 
J

JDaywalt

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

PCLIVE said:
Calculations are probably set to Manual.

Tools|Options|Calculation|Automatic

HTH,
Paul

--

JDaywalt said:
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?
Click to expand...
Click to expand...
 
J

Jim Thomlinson

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 said:
No they are not set to manual. Any other thoughts?

PCLIVE said:
Calculations are probably set to Manual.

Tools|Options|Calculation|Automatic

HTH,
Paul

--

JDaywalt said:
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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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.


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?
Click to expand...
 
J

JDaywalt

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 said:
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.


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?
Click to expand...
Click to expand...
 
D

Dave Peterson

Is calculation set to automatic?

What formula did you use--maybe you missed one???
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 said:
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.


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?
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Thomlinson

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 said:
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 said:
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.


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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File name to Cell 5
Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox 0
Tab Name Reference 5
Recalculate formulas when active sheet switches 1
range name as file name 3
Macro that will insert a simple text string in top cell of text file 1
Sheet Names and Auto-calculation 2
Obtain Path on open workbook 5

Top