Linking two cells in two books

S

Stephen

Hi,

I have two workbooks. In Book1, I want A2 to be exactly
the same as B1 in Book2.

The problem is that every night, I run a program which
updates Book2, inserting a new row 1, so the value in B1
is now pushed down to be the value in B2 and a new value
is inserted into in B1. I want this new value in B1 to be
reflected in A2 (Book1). That is, for A2 (Book1) to
always be the same value as B1 (Book2), even when B1
becomes a new row with a new value.

I thought I would be okay if I used dollar signs, so for
A2 in Book1, I have:

='[Book2.xls]AA'!$B$1

But it doesn't seem to be working.

Any help would be greatly appreciated.

Thanks a lot,

Stephen
 
M

Max

='[Book2.xls]AA'!$B$1

Try instead in Book1:
=INDIRECT("'[Book2.xls]AA'!$B$1")
which will always point to the target cell in Book2

Note: Book2 has to open for the above to work,
otherwise you'll get #REF!
 
S

Stephen

Hi,

Thanks for your help.

Book2 can be open when the update happens but it isn't
always open. (It's actually several books, I was just
trying to simplify the example.)

Must book2 always be open or just when the Update
(inserting of the new row, etc.) takes place?

Thanks again,

Stephen
-----Original Message-----
='[Book2.xls]AA'!$B$1
Click to expand...

Try instead in Book1:
=INDIRECT("'[Book2.xls]AA'!$B$1")
which will always point to the target cell in Book2

Note: Book2 has to open for the above to work,
otherwise you'll get #REF!
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Hi,

I have two workbooks. In Book1, I want A2 to be exactly
the same as B1 in Book2.

The problem is that every night, I run a program which
updates Book2, inserting a new row 1, so the value in B1
is now pushed down to be the value in B2 and a new value
is inserted into in B1. I want this new value in B1 to be
reflected in A2 (Book1). That is, for A2 (Book1) to
always be the same value as B1 (Book2), even when B1
becomes a new row with a new value.

I thought I would be okay if I used dollar signs, so for
A2 in Book1, I have:

='[Book2.xls]AA'!$B$1

But it doesn't seem to be working.

Any help would be greatly appreciated.

Thanks a lot,

Stephen
Click to expand...


.
Click to expand...
 
M

Max

Stephen said:
Must book2 always be open or just when the Update
(inserting of the new row, etc.) takes place?
Click to expand...

It's OK. Nothing to do with what's being
updated/happening to Book2.

The caveat was that if Book2 is not open
at the time you open Book1,
then the #REF! error will appear in Book1

If you see the error, just open Book2
and the error should go away /
INDIRECT formula would return correctly
 
Top