In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not recognize
a row deletion in the source sheet. Instead, the linked sheet displays a #Ref
message in the cell related to the deleted row.

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.

LeeC & Max -

The INIDRECT() function is necessary only if there's a chance that the first
row might be deleted. Also,

ROWS($A$1:A1)-1

can be shortened to

ROW()-1

So, if LeeC knows that the first row will never be deleted, this shorter
formula works, too:

=OFFSET(Sheet1!$A$1,ROW()-1,)


"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.

Thanks so much - - This shortened approach will work in some of our
scenarios. Thanks for your help, and your time!

"Duke Carey" wrote:

LeeC & Max -

The INIDRECT() function is necessary only if there's a chance that the first
row might be deleted. Also,

ROWS($A$1:A1)-1

can be shortened to

ROW()-1

So, if LeeC knows that the first row will never be deleted, this shorter
formula works, too:

=OFFSET(Sheet1!$A$1,ROW()-1,)


"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.

Thanks, Duke. Agreed there's shorter versions. But for increased robustness'
sake, thought the extra INDIRECT wrap, and the use of the slightly longer
"ROWS($A$1:A1)-1" over "ROW()-1" was worth it in this instance ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Duke Carey" wrote in message
...
LeeC & Max -

The INIDRECT() function is necessary only if there's a chance that the
first
row might be deleted. Also,

ROWS($A$1:A1)-1

can be shortened to

ROW()-1

So, if LeeC knows that the first row will never be deleted, this shorter
formula works, too:

=OFFSET(Sheet1!$A$1,ROW()-1,)

Thank you so very much! We (my associate and I) were searching everywhere in
"Help", certain that this could be done. Again - Thanks!

"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.

You're welcome !
Glad it helped ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
Thank you so very much! We (my associate and I)
were searching everywhere in
"Help", certain that this could be done. Again - Thanks!

Max wrote...
....
In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)
....

Very baroque, almost rococo.

Since your formula is meant to give Sheet1!A1 in Sheet2!A1, Sheet1!A99
in Sheet2!A99, etc., better to simplify.

=INDIRECT("Sheet1!RC",0)

Note that this doesn't need any changes to be used in other columns.

"Harlan Grove" wrote
....
Since your formula is meant to give Sheet1!A1 in Sheet2!A1,
Sheet1!A99 in Sheet2!A99, etc..

Just a clarification. No, not necessarily. Perhaps it might have been more
illustrative to have described the example starting cell in Sheet2 as say,
B2, rather than the coincident "A1"

=INDIRECT("Sheet1!RC",0)

Interestingly concise! Thanks. But how could the above be modified / adapted
so that the formula can be placed in any starting cell in Sheet2, say in B2
and copied down to link to Sheet1's col A, say?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max wrote...
....
Interestingly concise! Thanks. But how could the above be modified /
adapted
so that the formula can be placed in any starting cell in Sheet2, say
in B2
and copied down to link to Sheet1's col A, say?

With hardcoding, so Sheet2!B2 returns the value of Sheet1!A1,

B2:
=INDIRECT("Sheet1!R[-1]C[-1]",0)

Hardcoding only the source worksheet name and top-left result cell
address,

B2:
=INDIRECT("Sheet1!R"&ROWS($B$2:B2)&"C"&COLUMNS($B$ 2:B2),0)

Thanks, Harlan !
Good alternatives to learn ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sounds like just what I need, too: but being a newbie, your instructions have
me stumped. How/where do I enter the "=OFFSET..." formula? When I click the
formula bar icon, all I'm allowed to do is select a pre-existing formula from
a list

I've already got words in all the cells of the column where I want to put
the formula. Will the formual wipe out the words?


"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.

Yes, the formula will wipe out all the words if you put them in the same
cell, I believe the point with Max's formulas was to use another sheet
for the formulas thus link the from that sheet. You enter a formula by
selecting a cell and start by typing equal sign then type the formula, or
you can select a cell and click in the formula bar and enter the formula
there

--

Regards,

Peo Sjoblom


"BobW" wrote in message
...
Sounds like just what I need, too: but being a newbie, your instructions
have
me stumped. How/where do I enter the "=OFFSET..." formula? When I click
the
formula bar icon, all I'm allowed to do is select a pre-existing formula
from
a list

I've already got words in all the cells of the column where I want to put
the formula. Will the formual wipe out the words?


"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions
in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to
another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use
the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays
a
#Ref
message in the cell related to the deleted row.

What I have is this kind of table:

Evaluation Criteria Rating
Easy to Use yes
Intuitive Design yes
Windows Compatible no

etc.

I need to have a second sheet with the same Column A items, but different
Column B content....and they need to be linked so that if I insert another
Evaluation criterion (or delete one) on sheet A, the corresponding row will
also appear or disappear on sheet B.. But, entering the suggested formula in
each column A cell, will wipe out the data in that column. Hopefully someone
will know how to link the Column As on both sheets without requiring a
formula to occupy each cell of column A.

Not sure how your suggestion about using another sheet for the formulas,
would solve my problem, but, me being a newbie, it's not surprising! :-)



"Peo Sjoblom" wrote:

Yes, the formula will wipe out all the words if you put them in the same
cell, I believe the point with Max's formulas was to use another sheet
for the formulas thus link the from that sheet. You enter a formula by
selecting a cell and start by typing equal sign then type the formula, or
you can select a cell and click in the formula bar and enter the formula
there

--

Regards,

Peo Sjoblom


"BobW" wrote in message
...
Sounds like just what I need, too: but being a newbie, your instructions
have
me stumped. How/where do I enter the "=OFFSET..." formula? When I click
the
formula bar icon, all I'm allowed to do is select a pre-existing formula
from
a list

I've already got words in all the cells of the column where I want to put
the formula. Will the formual wipe out the words?


"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions
in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to
another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use
the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays
a
#Ref
message in the cell related to the deleted row.

Here's a sample construct:
http://cjoint.com/?lrxU7szxxX
BobW_misc.xls

Assuming the source table is in Sheet1, cols A to C, with Evaluation in col
A

In Sheet2,

Put in A2, copy down:
=IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"",
OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,))

Sheet2 will reflect the row deletions / insertions made in Sheet1
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Is it possible to do modify this for multiple columns at a time or would I
have to do this for each column? So, I want to be able to do the same thing,
but for a whole range instead of just one column.

"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.

Oh and also to make it so that blank cells in the source are also blank in
the destination (instead of a 0) Thanks!

"Max" wrote:

One way to try ..

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"LeeC" wrote in message
...
In Office XP - Excel - I want to copy a column from one sheet to another
sheet in the same workbook. I want the copied links to recognize row
deletions and insertions when I change the source sheet. When I use the
Copy, Paste Special, Paste Links command, the linked sheet does not
recognize
a row deletion in the source sheet. Instead, the linked sheet displays a
#Ref
message in the cell related to the deleted row.