Copying columns and automating data updates across worksheets
 

Sheet #1 is the core worksheet. Other sheets will draw from Sheet #1. For
example, I would like to know how I can set up Sheet #2 so that Columns A, B,
C and D will always be the same as Columns A, B, C and D on Sheet #1. So,
if I add a row to Sheet # 1 or I change the data in a cell or cells in
Columns A, B, C, and/or D of Sheet #1, those changes should automatically
appear in Sheet #2. I have tried references and links, but if I insert a
row to Sheet #1, Sheet #2 does not reflect the change. Any ideas? THANKS.

Copying columns and automating data updates across worksheets
 

I think you can select all the sheets, along with sheet1, that you want to
replicate the actions on sheet1 and then perform your operation on sheet1.
This will replicate it on other sheets.

Hope I have got your question right.

"jkl" wrote:

Sheet #1 is the core worksheet. Other sheets will draw from Sheet #1. For
example, I would like to know how I can set up Sheet #2 so that Columns A, B,
C and D will always be the same as Columns A, B, C and D on Sheet #1. So,
if I add a row to Sheet # 1 or I change the data in a cell or cells in
Columns A, B, C, and/or D of Sheet #1, those changes should automatically
appear in Sheet #2. I have tried references and links, but if I insert a
row to Sheet #1, Sheet #2 does not reflect the change. Any ideas? THANKS.

Copying columns and automating data updates across worksheets
 

I tried this formula in columns A, B c & D of sheet2 (or any range you want)

=INDEX(Sheet1!$1:$65536,ROW(),COLUMN())

Now, even if you insert columns or rows in sheet1, the results will be
reflected in sheet2

If you don't want the results of the formula to show zero for any blank
cells in Sheet1, you can use the following formula:

=IF(ISBLANK(INDEX(Sheet1!$1:$65536,ROW(),COLUMN()) ),"",INDEX(Sheet1!$1:$65536,ROW(),COLUMN()))

Hope this is what you are looking for.

rgds

André
Sydney, Australia


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Changing data can be effected by linking the cells from one sheet to
another
like........

On sheet2 enter in a cell =sheet1!A1

This cell will update when changes are made to sheet1 A1

I think you have done this.

But, when it comes to inserting/deleting rows it doesn't work that way.

Sheets cannot be synchronized unless "grouped" or VBA is used to insert
the row
on sheet1 and sheet2 will be get the inserted row and a new link formula
copied
in.

Even "grouping" won't insert the new link formula(s) when sheet1 row is
inserted.


Gord Dibben MS Excel MVP


On Fri, 9 Feb 2007 14:23:00 -0800, jkl
wrote:

Sheet #1 is the core worksheet. Other sheets will draw from Sheet #1.
For
example, I would like to know how I can set up Sheet #2 so that Columns A,
B,
C and D will always be the same as Columns A, B, C and D on Sheet #1.
So,
if I add a row to Sheet # 1 or I change the data in a cell or cells in
Columns A, B, C, and/or D of Sheet #1, those changes should automatically
appear in Sheet #2. I have tried references and links, but if I insert a
row to Sheet #1, Sheet #2 does not reflect the change. Any ideas?
THANKS.

Copying columns and automating data updates across worksheets
 

Andre ~

Thanks very much. I appreciate the help.





"Andre Croteau" wrote:


I tried this formula in columns A, B c & D of sheet2 (or any range you want)

=INDEX(Sheet1!$1:$65536,ROW(),COLUMN())

Now, even if you insert columns or rows in sheet1, the results will be
reflected in sheet2

If you don't want the results of the formula to show zero for any blank
cells in Sheet1, you can use the following formula:

=IF(ISBLANK(INDEX(Sheet1!$1:$65536,ROW(),COLUMN()) ),"",INDEX(Sheet1!$1:$65536,ROW(),COLUMN()))

Hope this is what you are looking for.

rgds

André
Sydney, Australia


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Changing data can be effected by linking the cells from one sheet to
another
like........

On sheet2 enter in a cell =sheet1!A1

This cell will update when changes are made to sheet1 A1

I think you have done this.

But, when it comes to inserting/deleting rows it doesn't work that way.

Sheets cannot be synchronized unless "grouped" or VBA is used to insert
the row
on sheet1 and sheet2 will be get the inserted row and a new link formula
copied
in.

Even "grouping" won't insert the new link formula(s) when sheet1 row is
inserted.


Gord Dibben MS Excel MVP


On Fri, 9 Feb 2007 14:23:00 -0800, jkl
wrote:

Sheet #1 is the core worksheet. Other sheets will draw from Sheet #1.
For
example, I would like to know how I can set up Sheet #2 so that Columns A,
B,
C and D will always be the same as Columns A, B, C and D on Sheet #1.
So,
if I add a row to Sheet # 1 or I change the data in a cell or cells in
Columns A, B, C, and/or D of Sheet #1, those changes should automatically
appear in Sheet #2. I have tried references and links, but if I insert a
row to Sheet #1, Sheet #2 does not reflect the change. Any ideas?
THANKS.