 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Issue 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? Calculations are probably set to Manual. Tools|Options|Calculation|Automatic HTH, Paul >I have used this commonly recommended formula to populate the file name > within cell A1 of my worksheet: [quoted text clipped - 14 lines] > have > to hit F9 each time? No they are not set to manual. Any other thoughts? > Calculations are probably set to Manual. > [quoted text clipped - 21 lines] > > 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...  SignatureHTH... Jim Thomlinson > No they are not set to manual. Any other thoughts? > [quoted text clipped - 23 lines] > > > 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. > I have used this commonly recommended formula to populate the file name > within cell A1 of my worksheet: [quoted text clipped - 13 lines] > Is there an adjustment I can make to remedy this problem? Or do I just have > to hit F9 each time? SignatureDave 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? > Use: > [quoted text clipped - 29 lines] > > Is there an adjustment I can make to remedy this problem? Or do I just have > > to hit F9 each time? 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 [quoted text clipped - 38 lines] > > > > Dave Peterson SignatureDave 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. SignatureHTH... Jim Thomlinson > 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 [quoted text clipped - 34 lines] > > > Is there an adjustment I can make to remedy this problem? Or do I just have > > > to hit F9 each time? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.