Links between sheets in error due to insert rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have links set up between a number of complex worksheets and workbooks in
excel 2003. However, I find that when I insert rows or columns in the sheet
to which I'm linking, it throws off the value in the linking sheet.

In workbook #1, I have link <=[workbook #2.xls]worksheet!$B$10

If I go to Workbook #2 and insert a row or rows above row 10, the value
shown in Workbook #1 changes, because it is showing the new value in cell
B10. However, what I want to be able to do is link to the particular cell in
workbook #2 - so that I could insert and delete rows/columns, and the info in
that particular cell would still be the source of the link.

Thanks,
Sarah
 
You can use INDIRECT but you must always have the workbooks open if you do
not have it open you'll get an error

=INDIRECT("'[workbook #2.xls]worksheet'!$B$10")
 
If you name the cell (insert|name|define), then the link will follow that named
cell--no matter where it is.
I have links set up between a number of complex worksheets and workbooks in
excel 2003. However, I find that when I insert rows or columns in the sheet
to which I'm linking, it throws off the value in the linking sheet.

In workbook #1, I have link <=[workbook #2.xls]worksheet!$B$10

If I go to Workbook #2 and insert a row or rows above row 10, the value
shown in Workbook #1 changes, because it is showing the new value in cell
B10. However, what I want to be able to do is link to the particular cell in
workbook #2 - so that I could insert and delete rows/columns, and the info in
that particular cell would still be the source of the link.

Thanks,
Sarah
Click to expand...
 
Dave - that sounded simple enough, but I can't get it to work.

I've named the cells I need links to, and for a portion of the named linked
cells it works. For the other half of named linked cells it doesn't - it is
taking the data from i.e. A70, even though five rows have been added on top
and it should now be referencing A75. I named the cells so I don't know what
the issue is.

Do you have any suggestions?

Dave Peterson said:
If you name the cell (insert|name|define), then the link will follow that named
cell--no matter where it is.
I have links set up between a number of complex worksheets and workbooks in
excel 2003. However, I find that when I insert rows or columns in the sheet
to which I'm linking, it throws off the value in the linking sheet.

In workbook #1, I have link <=[workbook #2.xls]worksheet!$B$10

If I go to Workbook #2 and insert a row or rows above row 10, the value
shown in Workbook #1 changes, because it is showing the new value in cell
B10. However, what I want to be able to do is link to the particular cell in
workbook #2 - so that I could insert and delete rows/columns, and the info in
that particular cell would still be the source of the link.

Thanks,
Sarah
Click to expand...
Click to expand...
 
My guess is I don't know how to name properly and only got 50% correct by
chance. The Excel Help feature isn't very helpful when it comes to "naming".

Dave Peterson said:
I don't have a guess.

Since it worked for 50% of them, I'm guessing that you did something different
with the other 50%.

Any chance you sorted your data after you applied the name. That'll screw
things up, too.
Dave - that sounded simple enough, but I can't get it to work.

I've named the cells I need links to, and for a portion of the named linked
cells it works. For the other half of named linked cells it doesn't - it is
taking the data from i.e. A70, even though five rows have been added on top
and it should now be referencing A75. I named the cells so I don't know what
the issue is.

Do you have any suggestions?

Dave Peterson said:
If you name the cell (insert|name|define), then the link will follow that named
cell--no matter where it is.

Sarah wrote:

I have links set up between a number of complex worksheets and workbooks in
excel 2003. However, I find that when I insert rows or columns in the sheet
to which I'm linking, it throws off the value in the linking sheet.

In workbook #1, I have link <=[workbook #2.xls]worksheet!$B$10

If I go to Workbook #2 and insert a row or rows above row 10, the value
shown in Workbook #1 changes, because it is showing the new value in cell
B10. However, what I want to be able to do is link to the particular cell in
workbook #2 - so that I could insert and delete rows/columns, and the info in
that particular cell would still be the source of the link.

Thanks,
Sarah
Click to expand...
Click to expand...
Click to expand...
 
That Insert|name dialog isn't very user friendly.

If I want to look at names, I'll use Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You'll wonder how you lived without it!

My guess is I don't know how to name properly and only got 50% correct by
chance. The Excel Help feature isn't very helpful when it comes to "naming".

Dave Peterson said:
I don't have a guess.

Since it worked for 50% of them, I'm guessing that you did something different
with the other 50%.

Any chance you sorted your data after you applied the name. That'll screw
things up, too.
Dave - that sounded simple enough, but I can't get it to work.

I've named the cells I need links to, and for a portion of the named linked
cells it works. For the other half of named linked cells it doesn't - it is
taking the data from i.e. A70, even though five rows have been added on top
and it should now be referencing A75. I named the cells so I don't know what
the issue is.

Do you have any suggestions?

:

If you name the cell (insert|name|define), then the link will follow that named
cell--no matter where it is.

Sarah wrote:

I have links set up between a number of complex worksheets and workbooks in
excel 2003. However, I find that when I insert rows or columns in the sheet
to which I'm linking, it throws off the value in the linking sheet.

In workbook #1, I have link <=[workbook #2.xls]worksheet!$B$10

If I go to Workbook #2 and insert a row or rows above row 10, the value
shown in Workbook #1 changes, because it is showing the new value in cell
B10. However, what I want to be able to do is link to the particular cell in
workbook #2 - so that I could insert and delete rows/columns, and the info in
that particular cell would still be the source of the link.

Thanks,
Sarah
Click to expand...
Click to expand...
Click to expand...
 
Back
Top