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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
on the workbook open event you can place this line Application.Calculate
0
Lyubomir DimitrovReporting & 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
Bill GoldenExecutive Managing MemberAuthor Commented:
Professor, I am not sure what you mean by the term "on the workbook open event."
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks again to both of you.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.