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?

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 wrote:

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

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" wrote:

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 wrote:

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

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson