Linked Workbooks, data thrown off anytime new sequence is added

%3CLINGO-SUB%20id%3D%22lingo-sub-1187189%22%20slang%3D%22en-US%22%3ELinked%20Workbooks%2C%20data%20thrown%20off%20anytime%20new%20sequence%20is%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1187189%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20I'll%20try%20to%20explain%20this%20as%20best%20I%20can.%20I%20have%20two%20workbooks%3A%20I%20enter%20monthly%20financial%20updates%20into%20one%20(let's%20call%20this%20one%20the%20data%20workbook)%2C%20and%20the%20second%20one%20is%20composed%20of%20compiled%20data%20from%20the%20first%20(we'll%20call%20this%20one%20SG).%20The%20formulas%20in%20the%20SG%20correlate%20with%20specific%20sequences%20in%20the%20data%20workbook.%26nbsp%3B%20My%20problem%20is%20that%20any%20time%20I%20insert%20a%20new%20row%20to%20the%20data%20workbook%2C%20the%20formulas%20in%20the%20SG%20stay%20linked%20to%20the%20same%20cells%2C%20and%20my%20totals%20are%20thrown%20off%20(it's%20financial%20data%2C%20so%20this%20can%20be%20a%20big%20problem).%20Is%20there%20a%20way%20to%20have%20the%20formulas%20link%20to%20specific%20sequences%2C%20rather%20than%20specific%20cells%3F%20Here's%20an%20example%20of%20what%20I%20thought%20would%20fix%20it%2C%20but%20it%20did%20not.%20!%24AT%24392%3C%2FP%3E%3CP%3EI%20thought%20the%20%22%24%22%20would%20keep%20the%20formula%20linked%20to%20the%20sequence%20rather%20than%20the%20cell%2C%20but%20that%20doesn't%20appear%20to%20be%20the%20case.%20These%20workbooks%20are%20huge%2C%20so%20it's%20not%20always%20easy%20to%20catch%20when%20a%20formula%20has%20%22slipped%22%2C%20any%20help%20would%20be%20extremely%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1187189%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-1187681%22%20slang%3D%22en-US%22%3ERe%3A%20Linked%20Workbooks%2C%20data%20thrown%20off%20anytime%20new%20sequence%20is%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1187681%22%20slang%3D%22en-US%22%3EIf%20you're%20inserting%20rows%2C%20make%20sur%20eboth%20workbooks%20are%20open%20in%20Excel.%20But%20to%20avoind%20problems%20like%20these%20I%20advise%20not%20to%20link%20this%20way%2C%20but%20rather%20pull%20in%20the%20data%20using%20Data%2C%20Get%20Data%2C%20From%20FIle%2C%20From%20workbook.%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi. I'll try to explain this as best I can. I have two workbooks: I enter monthly financial updates into one (let's call this one the data workbook), and the second one is composed of compiled data from the first (we'll call this one SG). The formulas in the SG correlate with specific sequences in the data workbook.  My problem is that any time I insert a new row to the data workbook, the formulas in the SG stay linked to the same cells, and my totals are thrown off (it's financial data, so this can be a big problem). Is there a way to have the formulas link to specific sequences, rather than specific cells? Here's an example of what I thought would fix it, but it did not. !$AT$392

I thought the "$" would keep the formula linked to the sequence rather than the cell, but that doesn't appear to be the case. These workbooks are huge, so it's not always easy to catch when a formula has "slipped", any help would be extremely appreciated. 

1 Reply
If you're inserting rows, make sur eboth workbooks are open in Excel. But to avoind problems like these I advise not to link this way, but rather pull in the data using Data, Get Data, From FIle, From workbook.