Maintaining Row Integrity with reference workbook

[kspencer]

I am using Excel 2003 to track some data between two workbooks. Book 1 has the source (first 6 columns of data) which I have referenced to Book 2. Book 2 contains the data from Book 1 but has additional columns of information that is manually updated.

Here's my problem. When Book 1 has an additional record inserted (not added at the bottom), Book 2 correctly inserts the record, but the manually added columns become offset. How can I get Book 1 to add a blank row with the inserted record and keep record/row integrity?

 

[aaronglover]

I am using Excel 2003 to track some data between two workbooks. Book 1 has the source (first 6 columns of data) which I have referenced to Book 2. Book 2 contains the data from Book 1 but has additional columns of information that is manually updated.

Here's my problem. When Book 1 has an additional record inserted (not added at the bottom), Book 2 correctly inserts the record, but the manually added columns become offset. How can I get Book 1 to add a blank row with the inserted record and keep record/row integrity?

Can you type in what formula you are using to link the two spreadsheets?

 

[kspencer]

Here's the formula for each cell I'm referencing from Book1:

='\\WorkbookNetworkLocation\[FileName.xls]SheetName'!A1

Thus is returns the data that is displayed in column A1 in Book 1.