linked workbook

[Guest]

how do i stop this?, when i sort the source workbook it
changes the info in the destination workbook and now the
linked cell has wrong data

 

[Myrna Larson]

What is the formula in the destination workbook?

 

[Guest]

='[FM Dealer List .xls]Dealers'!$K$3

-----Original Message-----
What is the formula in the destination workbook?



.

 

[Max]

how do i stop this?, when i sort the source workbook it
changes the info in the destination workbook and now the
linked cell has wrong data ......
='[FM Dealer List .xls]Dealers'!$K$3

Assume the link formula
is in say, A2: ='[FM Dealer List .xls]Dealers'!K3

Try instead in A2:

=INDIRECT("'[FM Dealer List .xls]Dealers'!"&ADDRESS(ROW(A3),COLUMN(K1),4))

The INDIRECT formula is the functional equivalent of
: ='[FM Dealer List .xls]Dealers'!K3 ]

except that it would always point to cell K3
in the particular source sheet/book
(and this is what you're after, I figure)

You could also just copy A2 down and across
to propagate the link relatively, viz., :

A2 copied down to A3 returns the functional equivalent
in A3: ='[FM Dealer List .xls]Dealers'!K4

A2 copied across to B3 returns the functional equivalent
in B3: ='[FM Dealer List .xls]Dealers'!L3

And so on ..

 

[Myrna Larson]

Your reference is absolute, to whatever is in cell K3. When you sort, the
contents of that cell change (which you already know). The formula in another
workbook won't change to "follow" the original cell contents. In fact a
formula *on the same sheet* as $K$3 will not do that.

You'll have to write a formula that links to the cell's *contents* rather than
its address. For example, if it's specific text, you could use something like

=VLOOKUP("Jones Brothers",'[FM Dealer List .xls]Dealers'!$K:$K,1,0)

What did cell K3 contain that you want to copy to your 2nd book?

='[FM Dealer List .xls]Dealers'!$K$3

-----Original Message-----
What is the formula in the destination workbook?



.

 

[Myrna Larson]

Hmmm... In my experience, sorting data in one workbook doesn't affect formulas
in other workbooks.

When I put data in Book1.xls!K1:K10, and in Book2.xls the formula
=Book1.xls!K3, then sort Book1, the formula in Book2 doesn't change, but of
course the formula *result* is probably different: it always returns the
*current contents* of Book1.xls!K3.

I interpreted his request to mean he wanted the 2nd workbook to show the same
*data* that was in $K$3 *before* the sort.

To get that, he'd have to eliminate updating Book2, which defeats the purpose
of a formula like this, doesn't it?

But my interpretation may be wrong. If it is, I don't see how your formula
differs from what he already has.

how do i stop this?, when i sort the source workbook it
changes the info in the destination workbook and now the
linked cell has wrong data .....
='[FM Dealer List .xls]Dealers'!$K$3

Assume the link formula
is in say, A2: ='[FM Dealer List .xls]Dealers'!K3

Try instead in A2:

=INDIRECT("'[FM Dealer List .xls]Dealers'!"&ADDRESS(ROW(A3),COLUMN(K1),4))

The INDIRECT formula is the functional equivalent of
: ='[FM Dealer List .xls]Dealers'!K3 ]

except that it would always point to cell K3
in the particular source sheet/book
(and this is what you're after, I figure)

You could also just copy A2 down and across
to propagate the link relatively, viz., :

A2 copied down to A3 returns the functional equivalent
in A3: ='[FM Dealer List .xls]Dealers'!K4

A2 copied across to B3 returns the functional equivalent
in B3: ='[FM Dealer List .xls]Dealers'!L3

And so on ..

 

[Max]

Yes, you're right, Myrna.
My goof. Please disregard earlier suggestion.
Think your interp. is probably right on

 

[Guest]

When I sort book1 the value in book2 changes because it
is looking in the same cell and it picked whatever is in
K3. I need for value in K3 book2 to also move when i sort
book1

 

[Guest]

the value in book1 contains numberic value in K3
associated to a customer. in book2 same customer, book2
needs values from book1 in K3. when i sort book1 value
from K3 in book2 should also move as customer row move in
book1

-----Original Message-----
Your reference is absolute, to whatever is in cell K3. When you sort, the
contents of that cell change (which you already know). The formula in another
workbook won't change to "follow" the original cell contents. In fact a
formula *on the same sheet* as $K$3 will not do that.

You'll have to write a formula that links to the cell's *contents* rather than
its address. For example, if it's specific text, you could use something like

=VLOOKUP("Jones Brothers",'[FM Dealer List .xls] Dealers'!$K:$K,1,0)

What did cell K3 contain that you want to copy to your 2nd book?

='[FM Dealer List .xls]Dealers'!$K$3

-----Original Message-----
What is the formula in the destination workbook?

wrote:

how do i stop this?, when i sort the source workbook it
changes the info in the destination workbook and now the
linked cell has wrong data

.

.

 

[Guest]

Myrna,

thanks, for responses. i probably should give more info:

book1 has customers and how many sales orders by date
i update book1 with orders values and then sort by date
to see recent activity by customer

book2 want the value for orders from book1 but when i
sort book1 to see recent activity book2 values changes
for customers due to it looking at cell K3

hope this is more clear.

thanks

-----Original Message-----
Hmmm... In my experience, sorting data in one workbook doesn't affect formulas
in other workbooks.

When I put data in Book1.xls!K1:K10, and in Book2.xls the formula
=Book1.xls!K3, then sort Book1, the formula in Book2 doesn't change, but of
course the formula *result* is probably different: it always returns the
*current contents* of Book1.xls!K3.

I interpreted his request to mean he wanted the 2nd workbook to show the same
*data* that was in $K$3 *before* the sort.

To get that, he'd have to eliminate updating Book2, which defeats the purpose
of a formula like this, doesn't it?

But my interpretation may be wrong. If it is, I don't see how your formula
differs from what he already has.

how do i stop this?, when i sort the source workbook it
changes the info in the destination workbook and now the
linked cell has wrong data .....
='[FM Dealer List .xls]Dealers'!$K$3

Assume the link formula
is in say, A2: ='[FM Dealer List .xls]Dealers'!K3

Try instead in A2:

=INDIRECT("'[FM Dealer List .xls]Dealers'!"&ADDRESS(ROW (A3),COLUMN(K1),4))

The INDIRECT formula is the functional equivalent of
: ='[FM Dealer List .xls]Dealers'!K3 ]

except that it would always point to cell K3
in the particular source sheet/book
(and this is what you're after, I figure)

You could also just copy A2 down and across
to propagate the link relatively, viz., :

A2 copied down to A3 returns the functional equivalent
in A3: ='[FM Dealer List .xls]Dealers'!K4

A2 copied across to B3 returns the functional equivalent
in B3: ='[FM Dealer List .xls]Dealers'!L3

And so on ..

#REF! errors

.

 

[Max]

As Myrna responded in the other thread,
you'd need to use VLOOKUP ..