SOLVED
Home

how to prevent a source workbook with external references to update all opened workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-638044%22%20slang%3D%22en-US%22%3Ehow%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638044%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20a%20source%20workbook%20containing%20all%20the%20latest%20prices%2C%20users%20create%20customized%20quotes.%20When%20opening%20a%20new%20quote%20file%2C%20the%20user%20select%20%22update%20links%22%20to%20get%20all%20the%20latest%20prices.%20But%20when%20he%20opens%20an%20old%20quote%20to%20compare%2C%20all%20opened%20quotes%20return%20to%20the%20old%20prices.%20How%20can%20the%20user%20open%20two%20excel%20files%20using%20the%20same%20source%20workbook%20but%20only%20have%20one%20updated%20and%20the%20other%20one%20not%20updated%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-638044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-638532%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638532%22%20slang%3D%22en-US%22%3EI%20would%20suggest%20to%20break%20the%20links%20to%20the%20pricing%20workbook%20once%20a%20quote%20has%20been%20completed.%20Otherwise%20you%20risk%20your%20prices%20to%20be%20updated%20even%20though%20you%20do%20not%20want%20them%20to.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-639242%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-639242%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20advice.%20Hum%2C%20the%20idea%20of%20linking%20the%20workbooks%20was%20that%20sales%20can%20update%20older%20quote%20if%20they%20wanted%20to%20continue%20with%20it.%20The%20prompt%20to%20update%20or%20not%20is%20great%20for%20that%20purpose.%20A%20formula%20with%20a%20timer%20at%20the%20top%20tells%20the%20salesperson%20when%20the%20quote%20is%20outdated%20and%20he%20can%20opt%20to%20update%20or%20keep.%20But%20after%20some%20time%2C%20a%20salesperson%20may%20want%20to%20create%20a%20new%20quote%20and%20compare%20it%20with%20the%20old%20obsolete%20one.%20For%20some%20reason%2C%20if%20the%20salesperson%20chooses%20not%20to%20update%20the%20old%20quote%2C%20the%20brand%20new%20which%20was%20updated%2C%20reverts%20to%20the%20old%20data.%20In%20other%20words%2C%20when%20the%20prompt%20to%20update%20or%20not%20update%20applies%20to%20every%20workbook%20opened%20on%20that%20computer%20and%20every%20workbook%20referenced.%20Is%20there%20a%20way%20to%20keep%20the%20source%20file%20update%20choice%20unique%20to%20each%20opened%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-639538%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-639538%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20probably%20not%20explaining%20well.%20Source%20workbook%20A%20and%20B%20contain%20price%20tables%20regularly%20updated.%20Quote%20file%20C%20and%20quote%20file%20D%20source%20their%20prices%20from%20A%20and%20some%20other%20prices%20from%20B.%20Quote%20C%20is%20a%20year%20old%20and%20the%20salesman%20chooses%20not%20to%20update.%20Instead%20he%20opens%20a%20brand%20new%20Quote%20D%20and%20updates%20from%20A%20and%20B.%20But%20he%20wants%20to%20see%20what%20were%20the%20items%20quoted%20in%20file%20C%20last%20year%2C%20so%20he%20opens%20file%20C%20and%20chooses%20Not%20to%20update.%20(He%20also%20wants%20to%20see%20the%20price%20difference%20between%20last%20year%20and%20now).%20To%20his%20surprise%2C%20all%20the%20prices%20in%20Quote%20files%20D%20which%20were%20just%20updated%2C%20have%20reverted%20to%20year%20old%20prices%20from%20file%20C.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643617%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643617%22%20slang%3D%22en-US%22%3EI'd%20say%20this%20is%20unexpected%20behaviour%20and%20clearly%20wrong.%20The%20only%20way%20to%20avoid%20this%20is%20by%20doing%20what%20I%20suggested%2C%20or%20by%20opening%20the%20other%20workbook%20in%20a%20new%20instance%20of%20Excel%20(hold%20the%20alt-key%20while%20you%20start%20Excel%20from%20the%20icon%20or%20the%20start%20menu).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644426%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644426%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20it%20really%20seems%20there%20is%20a%20bug%20in%20Excel.%20In%20the%20Edit%20Link%20box%2C%20I%20click%20on%20Startup%20prompt%20button%20and%20select%20%22don't%20display%20the%20alert%20and%20%3CU%3Edon't%20update%20automatic%20link%3C%2FU%3E%22%20and%20yet%20if%20I%20have%20another%20file%20open%20with%20the%20automatic%20update%20%22on%22%20and%20referring%20to%20a%20common%20source%20file%2C%20the%20first%20Excel%20file%20continues%20to%20update%20automatically.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644595%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20a%20source%20workbook%20with%20external%20references%20to%20update%20all%20opened%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644595%22%20slang%3D%22en-US%22%3EI%20suggest%20that%20you%20click%20the%20FIle%20menu%2C%20Feedback%20button%20(close%20to%20the%20bottom%20of%20the%20screen)%20and%20send%20a%20frown.%20MSFT%20does%20look%20at%20those.%3C%2FLINGO-BODY%3E
Stephen_Chayer
New Contributor

With a source workbook containing all the latest prices, users create customized quotes. When opening a new quote file, the user select "update links" to get all the latest prices. But when he opens an old quote to compare, all opened quotes return to the old prices. How can the user open two excel files using the same source workbook but only have one updated and the other one not updated?

6 Replies
I would suggest to break the links to the pricing workbook once a quote has been completed. Otherwise you risk your prices to be updated even though you do not want them to.

@Jan Karel Pieterse 

Thank you for the advice. Hum, the idea of linking the workbooks was that sales can update older quote if they wanted to continue with it. The prompt to update or not is great for that purpose. A formula with a timer at the top tells the salesperson when the quote is outdated and he can opt to update or keep. But after some time, a salesperson may want to create a new quote and compare it with the old obsolete one. For some reason, if the salesperson chooses not to update the old quote, the brand new which was updated, reverts to the old data. In other words, when the prompt to update or not update applies to every workbook opened on that computer and every workbook referenced. Is there a way to keep the source file update choice unique to each opened workbook?

I'm probably not explaining well. Source workbook A and B contain price tables regularly updated. Quote file C and quote file D source their prices from A and some other prices from B. Quote C is a year old and the salesman chooses not to update. Instead he opens a brand new Quote D and updates from A and B. But he wants to see what were the items quoted in file C last year, so he opens file C and chooses Not to update. (He also wants to see the price difference between last year and now). To his surprise, all the prices in Quote files D which were just updated, have reverted to year old prices from file C.  

I'd say this is unexpected behaviour and clearly wrong. The only way to avoid this is by doing what I suggested, or by opening the other workbook in a new instance of Excel (hold the alt-key while you start Excel from the icon or the start menu).
Highlighted

Thank you for the help.

 

However it really seems there is a bug in Excel. In the Edit Link box, I click on Startup prompt button and select "don't display the alert and don't update automatic link" and yet if I have another file open with the automatic update "on" and referring to a common source file, the first Excel file continues to update automatically. 

Solution
I suggest that you click the FIle menu, Feedback button (close to the bottom of the screen) and send a frown. MSFT does look at those.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies