fixed links to another workbook

[Jen]

Ordinary links work like a window, looking in at another workbook. The link
is to the cell regardless of what is in it. Adding rows or columns in the
source file which shifts the data around effectively breaks the link because
even though the link is still looking at cell B3, the relevant data is now in
cell B5. Is there a way to link to the value of the cell so the link would
update itself if the linked data gets moved?

 

[Dave Peterson]

If you open the file with the link to the original file, then the formulas that
point back will adjust.

If you don't have too many links, maybe you can name the cells that get
retrieved. In xl2003 menus, it's Insert|Name|Define.

Then point at that name in your formula.

 

[Jen]

Unfortunately, I have found that not to be true. I have a large workbook that
contains references to several other workbooks. Every time I edit the
individual workbooks, I need to go back into the compilation book and edit
the formulas to re-point to the data that got moved.

 

[Dave Peterson]

My suggestion was to open all the files in one instance of excel at the same
time. Make the changes and save the files nicely (in order).

That doesn't work for you?

 

[Jen]

Ah. Thank you for the clarification. With all of the workbooks open at the
same time, the formulas do update.