HELP! I cannot figure out what is wrong. Using Excel 2000, I have two
workbooks, one of which is a summary workbook linked to multiple rows in a
detailed workbook. For example, in the Summary Workbook there is a formula
which links to (for example) cells in row 50 in the detailed workbook.
everything is fine until I insert a new row above row 50 in the detailed
workbook. That, of course, changes the old row 50 to row 51. But when I
open the Summary Workbook, that reference has not changed or been updated.
It is still referring to row 50. The cell references are relative, not
absolute. What is wrong? I need the cell references in the Summary workbook
to update if there have been new rows added to the detailed workbook. Can
anyone help me PLEASE?
--
LPS

Your Summary workbook has no way to know that, while it was closed, you moved
things around in the detail workbook. There are two ways to avoid this
problem:
1) Open BOTH workbooks before you make changes in the detail workbook that
will move any of the linked cells. If both workbooks are open, the links stay
in synch.
2) Assign range names to the cells in the detail workbook to which the
Summary workbook will be linked. I always do this. If the linked cell in the
detail workbook has a name, the Summary workbook can find it even if it has
moved because of new rows or columns. Also, the link in the Summary workbook
will show the range name. If you use meaningful names, that provides
information about what the link represents.

Hope this helps,

Hutch

"LPS" wrote:

HELP! I cannot figure out what is wrong. Using Excel 2000, I have two
workbooks, one of which is a summary workbook linked to multiple rows in a
detailed workbook. For example, in the Summary Workbook there is a formula
which links to (for example) cells in row 50 in the detailed workbook.
everything is fine until I insert a new row above row 50 in the detailed
workbook. That, of course, changes the old row 50 to row 51. But when I
open the Summary Workbook, that reference has not changed or been updated.
It is still referring to row 50. The cell references are relative, not
absolute. What is wrong? I need the cell references in the Summary workbook
to update if there have been new rows added to the detailed workbook. Can
anyone help me PLEASE?
--
LPS

Hi Hutch... THANK YOU!!! I suspected as much and, given that there are
numerous users accessing these files at different times, I think that the
named ranges is the way to go.

Thank you again, for your expertise.
--
LPS


"Tom Hutchins" wrote:

Your Summary workbook has no way to know that, while it was closed, you moved
things around in the detail workbook. There are two ways to avoid this
problem:
1) Open BOTH workbooks before you make changes in the detail workbook that
will move any of the linked cells. If both workbooks are open, the links stay
in synch.
2) Assign range names to the cells in the detail workbook to which the
Summary workbook will be linked. I always do this. If the linked cell in the
detail workbook has a name, the Summary workbook can find it even if it has
moved because of new rows or columns. Also, the link in the Summary workbook
will show the range name. If you use meaningful names, that provides
information about what the link represents.

Hope this helps,

Hutch

"LPS" wrote:

HELP! I cannot figure out what is wrong. Using Excel 2000, I have two
workbooks, one of which is a summary workbook linked to multiple rows in a
detailed workbook. For example, in the Summary Workbook there is a formula
which links to (for example) cells in row 50 in the detailed workbook.
everything is fine until I insert a new row above row 50 in the detailed
workbook. That, of course, changes the old row 50 to row 51. But when I
open the Summary Workbook, that reference has not changed or been updated.
It is still referring to row 50. The cell references are relative, not
absolute. What is wrong? I need the cell references in the Summary workbook
to update if there have been new rows added to the detailed workbook. Can
anyone help me PLEASE?
--
LPS