Can a table be linked

[Linda]

It's been requested that a table in one worksheet be linked to the same table
in another worksheet. Can Excel update ALL information changed in the first
table to the linked table without having to link each cell separately?

 

[JLatham]

No (and yes)

Each of the cells will have to be linked, but you don't have to do each one
individually one-by one.

Let's say your source table in WBook1 begins in cell A1 on some sheet in it,
and the table in the other workbook, WBook2, begins at cell C1 (although it
could also begin at A1, I'm using 2 different cells to help keep things
clear).

Go to C1 in WBook2 and type the = symbol then choose WBook1 the proper
sheet and click in cell A1 and hit the [Enter] key. This sets up one link at
the upper left corner of the tables. The formula in C1 probably looks
something like:
=[WBook1]Sheet1!$A$1
edit that formula to look like
=[WBook1]Sheet1!A1
Notice that all I did was remove the $ symbols.
Now you can move your cursor to the lower right corner of C1 until it turns
into a small + symbol instead of the huge cross and drag it to the right and
down (you may have to drag in one direction, then select all and drag in the
other, i.e. right then down) until you've covered the same number of cells as
are in your original table. Voila'! the table in WBook2 is now linked to the
table in WBook1.

 

[Gord Dibben]

Wouldn't Paste Special>Paste Link do this all in one step?

i.e. select the range in sheet1 then paste special>paste link to C1 in
sheet2


Gord

No (and yes)

Each of the cells will have to be linked, but you don't have to do each one
individually one-by one.

Let's say your source table in WBook1 begins in cell A1 on some sheet in it,
and the table in the other workbook, WBook2, begins at cell C1 (although it
could also begin at A1, I'm using 2 different cells to help keep things
clear).

Go to C1 in WBook2 and type the = symbol then choose WBook1 the proper
sheet and click in cell A1 and hit the [Enter] key. This sets up one link at
the upper left corner of the tables. The formula in C1 probably looks
something like:
=[WBook1]Sheet1!$A$1
edit that formula to look like
=[WBook1]Sheet1!A1
Notice that all I did was remove the $ symbols.
Now you can move your cursor to the lower right corner of C1 until it turns
into a small + symbol instead of the huge cross and drag it to the right and
down (you may have to drag in one direction, then select all and drag in the
other, i.e. right then down) until you've covered the same number of cells as
are in your original table. Voila'! the table in WBook2 is now linked to the
table in WBook1.

It's been requested that a table in one worksheet be linked to the same table
in another worksheet. Can Excel update ALL information changed in the first
table to the linked table without having to link each cell separately?

 

[JLatham]

No doubt that it would! I just didn't think about that when I wrote my
response - low blood sugar, no doubt! Thanks for the reminder.

Wouldn't Paste Special>Paste Link do this all in one step?

i.e. select the range in sheet1 then paste special>paste link to C1 in
sheet2


Gord

No (and yes)

Each of the cells will have to be linked, but you don't have to do each one
individually one-by one.

Let's say your source table in WBook1 begins in cell A1 on some sheet in it,
and the table in the other workbook, WBook2, begins at cell C1 (although it
could also begin at A1, I'm using 2 different cells to help keep things
clear).

Go to C1 in WBook2 and type the = symbol then choose WBook1 the proper
sheet and click in cell A1 and hit the [Enter] key. This sets up one link at
the upper left corner of the tables. The formula in C1 probably looks
something like:
=[WBook1]Sheet1!$A$1
edit that formula to look like
=[WBook1]Sheet1!A1
Notice that all I did was remove the $ symbols.
Now you can move your cursor to the lower right corner of C1 until it turns
into a small + symbol instead of the huge cross and drag it to the right and
down (you may have to drag in one direction, then select all and drag in the
other, i.e. right then down) until you've covered the same number of cells as
are in your original table. Voila'! the table in WBook2 is now linked to the
table in WBook1.

It's been requested that a table in one worksheet be linked to the same table
in another worksheet. Can Excel update ALL information changed in the first
table to the linked table without having to link each cell separately?

.