Home

My whole Excel world is crumbling around me - interlinked workbooks using a lot of formulas and VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-691509%22%20slang%3D%22en-US%22%3EMy%20whole%20Excel%20world%20is%20crumbling%20around%20me%20-%20interlinked%20workbooks%20using%20a%20lot%20of%20formulas%20and%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691509%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20'set'%20of%20about%20a%20dozen%20spreadsheets%20through%20which%20I%20drive%20my%20family%20history%20projects.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20having%20problems%2C%20for%20quite%20some%20time%2C%20with%20their%20opening%20as%20a%20'set'%20and%20now%2C%20since%20the%20software%20updates%20which%20came%20through%20on%2013th%20June%2C%20many%20of%20the%20interlinked%20formulas%20are%20just%20failing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%20when%20they%20are%20opened%20up%2C%20through%20a%20.bat%20file%2C%20they%20open%20up%20with%20the%20Internet%20source%20warning%2C%20despite%20the%20fact%20that%20all%20the%20workbooks%2C%20and%20all%20the%20associated%20xlams%2C%20are%20on%20one%20of%20my%20own%20hard%20drives%20and%20I%20created%20them%20all.%20I%20have%20added%20the%20directories%20to%20my%20safe%20sources%20list%20but%20they%20still%20come%20up%20with%20this%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecondly%2C%20many%20of%20the%20formulas%20%2F%20workbook%20openings%20create%20messages%2C%20which%20say%20that%20the%20workbook%20has%20not%20been%20updated%20because%20the%20other%20spreadsheet%20was%20not%20recalculated%20before%20saving%20-%20such%20recalculation%20is%20not%20necessary%20in%20many%20cases.%20How%20can%20I%20remove%20this%20unnecessary%20check%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThirdly%2C%20some%20of%20the%20formulas%20are%20getting%20'corrupted'%20every%20time%20I%20close%20the%20file%2C%20so%20that%20I%20have%20had%20to%20keep%20a%20text%20copy%20of%20the%20formulas%20in%20the%20worksheet%2C%20so%20that%20I%20can%20copy%20and%20paste%20the%20uncorrupted%20text%20of%20the%20formulas%20back%20in%2C%20every%20time%20I%20re-open%20the%20affected%20spreadsheets%20-%20this%20is%20a%20most%20annoying%20and%20time%20wasting%20activity%20-%20any%20ideas%20would%20be%20welcome%2C%20please.%20One%20thought%20I%20have%20had%20is%20that%20these%20'corrupting'%20formulas%20are%20often%20quite%20complicated%2C%20often%20using%20quite%20a%20few%20nested%20IFs%20and%2For%20VLOOKUPs%20%5Bthe%20VLOOKUPs%20often%20being%20ones%20that%20look%20across%20to%20another%20workbook%5D%3A%20would%20it%20be%20better%20if%20I%20tried%20to%20turn%20these%20formulas%20into%20my%20own%20VBA%20functions%2C%20maybe%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20hopes%20for%20some%20ideas%2C%20please.%20I%20wish%20I%20could%20send%20someone%20the%20whole%20set%20of%20stuff%2C%20so%20that%20they%20could%20look%20for%20the%20flaws%20in%20my%20Excel%20use%2C%20but%20as%20a%20disabled%20pensioner%20I%20cannot%20afford%20to%20pay%20anyone%2C%20so%20I%20have%20to%20rely%20on%20Internet%20forums%20to%20try%20and%20get%20some%20help%2C%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPhilip%3CBR%20%2F%3EBendigo%2C%20Victoria%3CBR%20%2F%3EAustralia%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-691509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ebatch%20opening%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIF%20blocks%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMultiple%20workbooks%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENested%20constructs%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esafe%20sources%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
PMHunt1955
Contributor

I have a 'set' of about a dozen spreadsheets through which I drive my family history projects.

 

I have been having problems, for quite some time, with their opening as a 'set' and now, since the software updates which came through on 13th June, many of the interlinked formulas are just failing.

 

Firstly when they are opened up, through a .bat file, they open up with the Internet source warning, despite the fact that all the workbooks, and all the associated xlams, are on one of my own hard drives and I created them all. I have added the directories to my safe sources list but they still come up with this message.

 

Secondly, many of the formulas / workbook openings create messages, which say that the workbook has not been updated because the other spreadsheet was not recalculated before saving - such recalculation is not necessary in many cases. How can I remove this unnecessary check?

 

Thirdly, some of the formulas are getting 'corrupted' every time I close the file, so that I have had to keep a text copy of the formulas in the worksheet, so that I can copy and paste the uncorrupted text of the formulas back in, every time I re-open the affected spreadsheets - this is a most annoying and time wasting activity - any ideas would be welcome, please. One thought I have had is that these 'corrupting' formulas are often quite complicated, often using quite a few nested IFs and/or VLOOKUPs [the VLOOKUPs often being ones that look across to another workbook]: would it be better if I tried to turn these formulas into my own VBA functions, maybe?

 

With hopes for some ideas, please. I wish I could send someone the whole set of stuff, so that they could look for the flaws in my Excel use, but as a disabled pensioner I cannot afford to pay anyone, so I have to rely on Internet forums to try and get some help, please.

 

Best regards

 

Philip
Bendigo, Victoria
Australia

Related Conversations