How to force calculate an Excel sheet.

%3CLINGO-SUB%20id%3D%22lingo-sub-1550912%22%20slang%3D%22en-US%22%3EHow%20to%20force%20calculate%20an%20Excel%20sheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550912%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20taking%20in%205%20piece%20of%20data%20from%20a%20Form%20and%20adding%20rows%20to%20an%20Excel%20sheet.%20I%20have%20a%20pivot%20table%20to%20TEXTJOIN%20some%20of%20this%20data%20into%20a%20single%20cell%2C%20to%20be%20used%20as%20text%20in%20an%20email.%20After%20taking%20in%20the%20original%20data%2C%205%20steps%20later%2C%20when%20I%20send%20the%20email%2C%20the%20TEXTJOIN%20cell%20still%20has%20no%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20force%20calculate%20the%20workbook%20so%20that%20Excel%20has%20time%20to%20do%20these%205%20calculations%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%2020%20or%20so%20seconds%20that%20the%20intervening%20steps%20need%20is%20not%20enough.%20(I%20have%20read%20that%20other%20people's%20addition%20of%20a%20time%20delay%20of%20as%20much%20as%205%20minutes%20does%20nothing%20to%20help%20the%20results.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20with%20the%20sheet%20live%2C%20adding%20a%20piece%20of%20data%20to%20the%20source%20cells%20(almost)%20instantly%20adds%20them%20to%20the%20TEXTJOIN%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1550912%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551317%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20force%20calculate%20an%20Excel%20sheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660787%22%20target%3D%22_blank%22%3E%40Ben72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20provide%20sample%20file%20to%20illustrate%20to%20illustrate%20what%20are%20doing%2C%20it's%20fully%20not%20clear%2C%20at%20least%20for%20me.%20Staring%20from%20combining%20TEXTJOIN%20and%20PivotTable%2C%20how%20did%20you%20manage%20to%20do%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am taking in 5 piece of data from a Form and adding rows to an Excel sheet. I have a pivot table to TEXTJOIN some of this data into a single cell, to be used as text in an email. After taking in the original data, 5 steps later, when I send the email, the TEXTJOIN cell still has no data.

 

How can I force calculate the workbook so that Excel has time to do these 5 calculations?

 

The 20 or so seconds that the intervening steps need is not enough. (I have read that other people's addition of a time delay of as much as 5 minutes does nothing to help the results.)

 

Of course, with the sheet live, adding a piece of data to the source cells (almost) instantly adds them to the TEXTJOIN cell.

 

Thank you for your help.

1 Reply
Highlighted

@Ben72 

Could you please provide sample file to illustrate to illustrate what are doing, it's fully not clear, at least for me. Staring from combining TEXTJOIN and PivotTable, how did you manage to do that?