SOLVED

Linked formulas across books

%3CLINGO-SUB%20id%3D%22lingo-sub-2149448%22%20slang%3D%22en-US%22%3ELinked%20formulas%20across%20books%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149448%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20linked%20formulas%20from%20one%20excel%20book%20to%20another%20but%20the%20formulas%20only%20calculate%20when%20the%20other%20book%20is%20open.%26nbsp%3B%20How%20to%20fix%20so%20that%20it%20will%20automatically%20calculate%20without%20the%20other%20book%20being%20open%3F%26nbsp%3B%20The%20sheet%20is%20set%20to%20automatically%20calculate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2149448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2150363%22%20slang%3D%22en-US%22%3ERe%3A%20Linked%20formulas%20across%20books%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2150363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972349%22%20target%3D%22_blank%22%3E%40Tim_B50%3C%2FA%3E%26nbsp%3B%20All%20values%20in%20the%20sheet%20with%20linked%20values%20show%20as%20%23value.%26nbsp%3B%20When%20the%20linked%20sheet%20is%20opened%2C%20these%20values%20magically%20populate%20with%20the%20proper%20values.%26nbsp%3B%20If%20I%20close%20the%20linked%20sheet%2C%20the%20values%20remain.%26nbsp%3B%20However%2C%20when%20the%20sheet%20is%20saved%20and%20re-opened%2C%20the%20values%20have%20returned%20to%20%23value.%26nbsp%3B%20I'm%20lost%20on%20this%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Have linked formulas from one excel book to another but the formulas only calculate when the other book is open.  How to fix so that it will automatically calculate without the other book being open?  The sheet is set to automatically calculate.

2 Replies
best response confirmed by Tim_B50 (New Contributor)
Solution

@Tim_B50  All values in the sheet with linked values show as #value.  When the linked sheet is opened, these values magically populate with the proper values.  If I close the linked sheet, the values remain.  However, when the sheet is saved and re-opened, the values have returned to #value.  I'm lost on this one.

Linked formula used was =SUMIF(....) which does not work across Excel Workbooks unless both books are open. Changed reference to =sumproduct(....) and now works as designed.