Inserting rows in link source - doesn't show up in link destination?

[rastlouis]

Hi. Please help if you know an answer:
- I am trying to setup a workbook with 2 worksheets.
- The left column in each worksheet needs to be identical.
- I have setup a link from the contents of the left column in
worksheet1 to worksheet2 (copying the range in worksheet1, and pasting
as link into worksheet 2)
- When I change an entry in one of the linked range of cells in
worksheet1, the change appears in the corresponding cell in worksheet2.
good.
- When I insert a row in the middle of the dataset, worksheet1 shows
the new row, but not in worksheet2.

I'd like this to work so when I add or delete a row within the range of
linked cells in worksheet1, that corresponding row is added or deleteed
in worksheet2. Is this possible??

Thanks!

 

[RagDyer]

Assuming you're linking A1 on Sheet1 to Sheet2, and then going down the
column, try this formula on Sheet2, and copy down as needed:

=INDIRECT("Sheet1!A"&ROWS($1:1))

 

[rastlouis]

Thanks,
Tried the formula and it seems to work, but seems to result in
doubled-up rows in Sheet 2 (ie. the result concatenates 2 rows into 1).
Used instead =INDIRECT("Sheet1!A1") which seems to work. However, I
need to edit the A1 part when I copy it down the column, to manually
change the row number to it matches the corresponding row in Sheet 1.
Seems to work when I delete or add a row, or sort the contents of the
column in Sheet 1.
CHeers!

 

[RagDyer]

The formula I posted *will automatically* increment your row numbers as you
copy it down a column ... *AND* ... it will *only* return the contents of a
*single cell* per row.

I have no idea what you did for you to say that it returns anything else ! !
!

 

[rastlouis]

Thanks RD. Well, I tried it again and it works. As you said, I have
no idea what I did the first time, but it works now. Thanks a lot!

 

[RagDyer]

Glad you persisted.

Thanks for the feed-back.