linking documents so that one change n the other follows

R

Rae

Hi all,
was wondering if it is possible to link 2 documents, say if the
documents are almost identical say are just titled differently but
want the values in the cells of the 1st document be exactly the sam
with the second. What can I do so that I just have to type in 1 an
the other one will automatically changes with the 1st.
Thank you
 
M

Max

Assume you want to link what's on Sheet1 to Sheet2

One way ..

(other than copying Sheet1's range
and then paste special > paste link in Sheet2)

In Sheet2
-------------
Put in A1:
=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)

Copy A1 across and down
to cover the same range (or the "max" likely range)
as in the source Sheet1

For a neater look, extraneous zeros can
be suppressed from showing in Sheet2 via:
Tools > Options > View tab > Uncheck "Zero values" > OK

When new rows are inserted within the range covered in Sheet1,
the corresponding updates will be made in Sheet2
(unlike the paste special > paste link route)
 
R

Rae

Hi Max,
Tried ur method..but doesn't seems to work. When I tried to enter th
function, a window "Update Value:Sheet 1" with the folders list wil
pop up. And very sorry but I don't really understand the functio
{=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)}. What are all the "1
after the minus sign? Thank you very much and sorry for the trouble
 
M

Max

The example assumed that you have a source sheet named: Sheet1
from which you want to link correspondingly in another sheet named: Sheet2

Try again once you ensure that you actually have a sheet named: Sheet1 in
the book
{=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)}
Click to expand...
The formula above is entered *normally* in A1 of Sheet2, not array-entered
What are all the "1" after the minus sign?
Click to expand...
These are the adjustments within the OFFSET formula
so that it'll return what is desired. Check up more on OFFSET in Excel's
help.

If implemented properly, whatever's done in Sheet1 will be replicated*
in the same cells in Sheet2

*except cell formatting
 
Top