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


--

Regards,

Peo Sjoblom

"Sarah" wrote in message
...
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

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

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

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

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.

jhersko wrote:

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

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


--

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!


jhersko wrote:

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

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.

jhersko wrote:

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

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


--

Dave Peterson


--

Dave Peterson