Absolute value changing in linked workbooks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marnie - 12 May 2006 21:21 GMT

I want to link Row 3 in workbookA to Row 5 in workbookB. In WorkbookB cell
A5 I have:

=+[WorkbookA.xls]Sheet1!$A$3

The links are working except when I insert a row above Row 3 in WorkbookA.
It changes the absolute reference to $A$4. I want it to remain linked to row
3.

I would appreciate a suggestion.

Reply to this Message

JE McGimpsey - 13 May 2006 00:29 GMT

One way:

=INDEX([WorkbookA.xls]Sheet1!$A:$A,3)

Note that your leading + is superfluous in XL.

> I want to link Row 3 in workbookA to Row 5 in workbookB. In WorkbookB cell
> A5 I have:
[quoted text clipped - 6 lines]
>
> I would appreciate a suggestion.

Reply to this Message

Marnie - 15 May 2006 15:28 GMT

That worked! Thank you!

> One way:
>
[quoted text clipped - 12 lines]
> >
> > I would appreciate a suggestion.

Reply to this Message

Bill Manville - 16 May 2006 00:50 GMT

The other thing to do is to ensure WorkbookB is open when you are
modifying WorkbookA

Or if you name the cell in WorkbookA, for example "MyCell" then you can
insert and delete ranges that move MyCell and a link:
=[WorkbookA.xls]Sheet1!MyCell
will still work

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message