Preserving data integrity in linked workbooks

[Guest]

I have multiple "source" workbooks that are consolidated in a "target"
workbook. My concern is that the users may insert or delete rows, or
otherwise move data around, causing the target book to be in error. I have
been advised that the safest way to go is to link to named ranges rather than
cells.

Here is where I get confused:

In the Target workbook, Cell A1 shows the sum total of Cells B2:B5 in the
source book; Cell B1 shows the sum total of cells C2:C5, and so on. How do I
set the range names in the source book and how do I link to them?

 

[Dave Peterson]

First, could you just change your formulas to include all of column B?

If there are non-numeric headers in B1 and nothing under B5, then you could
just:
=SUM('C:\My Documents\excel\[book1.xls]Sheet1'!$B:$B)

If not, then I think I'd define a range that points at B2:H5 (H is my last used
column).

But I'd lock Row 5 and force them to insert rows between 2 and 5.

Then when the range expands, the name will adjust.

Inside each source workbook:
Insert|Name|Define
names in workbook: myRng (for my example)
refers to: =Sheet1!$B$2:$H$5

Then save and close that workbook.

Now adjust your formula to look like:
=SUM(INDEX('C:\My Documents\excel\book1.xls'!myRng,,1))

The final 1 represents the first column in that range.

So to sum the 4th column:
=SUM(INDEX('C:\My Documents\excel\book1.xls'!myRng,,4))

======
This kind of thing works ok with closed workbooks. If your source workbooks
were always open, you could use a dynamic range name.

kind of like:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$99),7)

That counts the cells used (no gaps) from B2:B99.

But this kind of formula won't work with a closed workbook. (I got #ref! errors
back.)

If you want to learn more about dynamic range names, visit Debra Dalgleish's
site:
http://www.contextures.com/xlNames01.html#Dynamic