Forcing recalculation of cells on opening an Excel spreadheet

Through EE the following Excel formula was developed to display a portion of the filename in a cell

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+5, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-22)

Unfortunately, periodically, but not always, on opening the spreadsheet the cell displays an incorrect value until you click on the cell and hit Enter.

Is there a way to force recalculation of all formulas, or certain cells, to recalculate on opening?
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
 
ProfessorJimJamConnect With a Mentor Commented:
the two formulas you wanted to be merged. you can merge them by & ampersand sign. here it is below.

the first formula was to extract portion of file name and the second one was to extract the sheet name. both combined show file name and sheet name.

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+5,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-14)&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

i have put the above formula in the attached workbook.

After everythign we tried.

i think the only culprit that will cause your formulas not to update is,  Your Excel file calculation setting must be in "Manual"
please see attached screenshot.   if it is currently in Manual. you need to Select "Automatic" then save and close and re open workbook. your formulas must work by then.
please let me know if it does not.
aa.pngLongNameofWorkbookLongLongLongLong.xlsx
0
 
ProfessorJimJamCommented:
on the workbook open event you can place this line Application.Calculate
0
 
Lyubomir DimitrovConnect With a Mentor Reporting & Excel SupportCommented:
Hi,

Worksheets(“YourSheetName”).Calculate Link

Otherwise you can go Formulas, Calculate Now (if you dont need a vba solution).

Shift+F9 will calculate your sheet, F9 will do the workbook

good luck,
Lyubo
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bill GoldenExecutive Managing MemberAuthor Commented:
Professor, I am not sure what you mean by the term "on the workbook open event."
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Lyubomir,  

I suppose this...

Worksheets(“YourSheetName”).Calculate Link

but I am not sure where you put the code. And, if it is VBA code that requires the name of the worksheet, that will not work since there are dozens of worksheets being used with different names.
0
 
ProfessorJimJamCommented:
please see attached video i recorded for you.

if to force calculate all sheets then Application.CalculateFull  

if you only want it for once sheet then instead of that above line put this   ThisWorkbook.Sheets("YourSheetName").Calculate  rename the Yoursheetname with actual sheet name
Video.mp4
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
I place the following...

Private Sub Workbook_Open()
Application.Calculate
End Sub

In Module1 section, Sheet1 section and ThisWorkbook section. No results.
0
 
ProfessorJimJamCommented:
Hi Bill,

did you see the video i uploaded?

you need to put the below, Not in the module but in the "ThisWorkbook" object  

Private Sub Workbook_Open()
Application.CalculateFull
End Sub
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Yes, I did watch the video. Thanks.
Unfortunately, I feel the law of unintended consequences circling here.  
The cells in question, same number cell on each worksheet, display a value based, in part, on their respective TabName, in this case 01-30.
So if you close the workbook setting on Tab 19, when you re-open the workbook, all the cells in question are using 19 instead of their respective Tab numbers.
0
 
ProfessorJimJamCommented:
Can you please close the workbook then open it again and this time press F9 key and then see if the values are changing. If it does then I can recommend another solution and if does not then we may use another method
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
No, the values are not changing when the file is closed, re-opened and F9 is pressed.
0
 
ProfessorJimJamCommented:
Please replace the Application.CalculationFull with

 application.calculatefullrebuild
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
If you can tell me how to piece together the two following formulas

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+5,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-14)

and

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

they accomplish the task.
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
I'm sorry, I forgot to respond to your last suggestion

application.calculatefullrebuild

had no effect
0
 
Lyubomir DimitrovReporting & Excel SupportCommented:
hi,

have you tried send keys? https://stackoverflow.com/questions/9096636/excel-2010-select-each-cell-in-a-row-activate-them-one-by-one

you can loop your worksheets and cells where it is necessary, but basically with send keys you are clicking and hitting enter should work.

all the best,
Lyubo
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks again to both of you.
0
 
ProfessorJimJamCommented:
You are welcome Bill.

Was the calculation setup manual?
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
No, it was automatic, but my flaky formula was causing the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.