I have this problem:

Book1 is my source and Book2 is my target. I have pasted the following link
in Book2 which returns the cell value of C10 in Book1. This works fine for me.

'[Book1.xls]Sheet1'!$C$10

However, if I try to change the above statement's cell address $C$10 to
$C$20 (say), it won't link to Book1 C20, instead it gives a TEXT content as

='[Book1.xls]Sheet1'!$C$20

Can anyone help to solve this puzzle? Thank you.

zhj23

see if the cell is formatted as text. if so, change to general then click on
your cell, hit F2, and enter.

another possibility is if show formulas is on (this can toggled on/off with
Ctrl ~, or Tools/Options/View - uncheck formulas), but I doubt it since your
first example calculated correctly.


"zhj23" wrote:

I have this problem:

Book1 is my source and Book2 is my target. I have pasted the following link
in Book2 which returns the cell value of C10 in Book1. This works fine for me.

'[Book1.xls]Sheet1'!$C$10

However, if I try to change the above statement's cell address $C$10 to
$C$20 (say), it won't link to Book1 C20, instead it gives a TEXT content as

='[Book1.xls]Sheet1'!$C$20

Can anyone help to solve this puzzle? Thank you.

zhj23

Thanks very much for your suggestion.

I think it is more important to format the SOURCE as GENERAL. If not, once
the first link is pasted (C10 in my example), the target would be pasted as
TEXT. Hence when the linked address is changed to C20, it wont work for the
target cell is now a TEXT cell.

Correct me if I am wrong.

zhj23


"JMB" wrote:

see if the cell is formatted as text. if so, change to general then click on
your cell, hit F2, and enter.

another possibility is if show formulas is on (this can toggled on/off with
Ctrl ~, or Tools/Options/View - uncheck formulas), but I doubt it since your
first example calculated correctly.


"zhj23" wrote:

I have this problem:

Book1 is my source and Book2 is my target. I have pasted the following link
in Book2 which returns the cell value of C10 in Book1. This works fine for me.

'[Book1.xls]Sheet1'!$C$10

However, if I try to change the above statement's cell address $C$10 to
$C$20 (say), it won't link to Book1 C20, instead it gives a TEXT content as

='[Book1.xls]Sheet1'!$C$20

Can anyone help to solve this puzzle? Thank you.

zhj23

Yes - if the source cell is formatted as text, the target cell will be
formatted as text when you copy/paste. Try changing the source cell format
before copy/pasting or the destination cell format after copy/pasting -
whichever way will work best for you.



"zhj23" wrote:

Thanks very much for your suggestion.

I think it is more important to format the SOURCE as GENERAL. If not, once
the first link is pasted (C10 in my example), the target would be pasted as
TEXT. Hence when the linked address is changed to C20, it wont work for the
target cell is now a TEXT cell.

Correct me if I am wrong.

zhj23


"JMB" wrote:

see if the cell is formatted as text. if so, change to general then click on
your cell, hit F2, and enter.

another possibility is if show formulas is on (this can toggled on/off with
Ctrl ~, or Tools/Options/View - uncheck formulas), but I doubt it since your
first example calculated correctly.


"zhj23" wrote:

I have this problem:

Book1 is my source and Book2 is my target. I have pasted the following link
in Book2 which returns the cell value of C10 in Book1. This works fine for me.

'[Book1.xls]Sheet1'!$C$10

However, if I try to change the above statement's cell address $C$10 to
$C$20 (say), it won't link to Book1 C20, instead it gives a TEXT content as

='[Book1.xls]Sheet1'!$C$20

Can anyone help to solve this puzzle? Thank you.

zhj23