Inserted rows in source workbook are throwing off my formulas

[Bickitori]

I am using several source workbooks to compile some numbers into a separate workbook. When my numbers didn't balance, I discovered that a row was inserted in one of my source workbooks, which threw off over half of my cell references. The inserting of the rows is going to be an ongoing issue; how do I set it up so when I open my separate workbook, it knows that the rows have been added and to compensate? A fix to this would save me an inordinate amount of time.

As a side note I'm somewhere between a beginner and moderate user of Excel, so the less complicated your replies are the better I can understand them. Also, I'm using Excel 2003.

Thanks for any and all help!

[rwgrietveld]

Dear Bickitori,

What you're asking is somesort of a check. This can be done in many ways and I think you're problem is solvable, but what we need to know is the exact data you are referencing to.
Is the inserted row blank?
Is there a unique key. Meaning data in that column is unique, descending/Ascending.
To sounds like functions like Find, Match, Search, Lookup, but without this info we can't tell. Please add a workbook with data.

[Bickitori]

I've attached my workbook, the one that is combining data from the others. I'm unable to attach any of the source documents because they are huge. The data isn't organized in any specific way. It's simply a spreadsheet to keep track of sales by office and sales rep, so no sorting is done. The AllSitesRevenueWks25to37 has all of the sales reps, one line per rep and I've gone into each of my four source workbooks and just added the cells for the week, for that sales rep. If a sales rep makes a sale that needs to be accounted for and that sales rep isn't already on one of the four source documents, they have to be added, hence the inserting of the row. Each column is a week. Week 40 has come and gone, all data is good and my numbers balance. Week 41 is when the row was added, but it will affect week 40.

I vaguely remember being told a long time ago that if I open the source documents first, then open my document and update it as usual, then it will find the appropriate data. However, I realize that this could be completely wrong! I haven't tried it yet as I'd rather not do something that's not going to work and that I have to undo. Thanks!

[rwgrietveld]

Please simplify. These sheets are to extensive, protected and I can't see any relationship. What is it that you expect? Use terms like.
I hava source files with data.
This data needs to be consolidated.
etc.