How do I link cells, sheet to sheet, to recognize row deletions?

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

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 atyahoodotcom
----
"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 atyahoodotcom
----
"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 atyahoodotcom
----
"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
--

Max, YOU THE MAN! Thank you for the example file...just what i needed!

"Max" wrote:

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
--

Glad it helped, BobW !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"BobW" wrote in message
...
Max, YOU THE MAN! Thank you for the example file...just what i needed!

Max, I love how it works, but...still don't understand where/how you inserted
the formula. Remember, I'm a newbie

"Max" wrote:

Glad it helped, BobW !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"BobW" wrote in message
...
Max, YOU THE MAN! Thank you for the example file...just what i needed!

"BobW" wrote
.. where/how you inserted the formula.

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,))

From the above lines, the formula is placed in Sheet2's A2,
and then copied down

I'd select the tab: Sheet2, then select the cell A2, click inside the
formula bar, then either type-out the formula, or paste it in (if I had
copied it earlier). And then I'd press ENTER to confirm the formula. To copy
the formula in A2 down, I'd re-select A2 and point the cursor at its fill
handle, i.e. the little black solid box at the bottom right corner of A2
(the cursor will also turn into a "black cross"), and drag it down as far as
I want.

Hope the above helps ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Max,
I notice that in sheet 2, row 1 of your example, there's this formula:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Whereas the formula for row 2 is more complicated:
=IF(OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,)=0,"",
OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A2)-1,))

What's the purpose of the Row 1 formula...and what if I need three or four
additional header rows instead of just one row...do I just change the "A2"s
throughout, to "A5" or "A6", etc?

Also,

"Max" wrote:

"BobW" wrote
.. where/how you inserted the formula.

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,))

From the above lines, the formula is placed in Sheet2's A2,
and then copied down

I'd select the tab: Sheet2, then select the cell A2, click inside the
formula bar, then either type-out the formula, or paste it in (if I had
copied it earlier). And then I'd press ENTER to confirm the formula. To copy
the formula in A2 down, I'd re-select A2 and point the cursor at its fill
handle, i.e. the little black solid box at the bottom right corner of A2
(the cursor will also turn into a "black cross"), and drag it down as far as
I want.

Hope the above helps ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--