Links between sheets in error due to insert rows

[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

 

[Peo Sjoblom]

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")

 

[Dave Peterson]

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

 

[Guest]

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.

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

 

[Guest]

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".

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

 

[Dave Peterson]

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".

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