Copying columns and automating data updates across worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
Worked like a charm. Thanks Harshad.

Harshad said:
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.
Click to expand...
 
Thanks.

Therein lies the rub . . . I'm trying to get sheet #2 sheet to be an ongoing
automatic derivative of sheet #1 and any data or edits entered in sheet#1.
Trial and error at this end totally meshes with your post. If I start trying
to use linking, things go awry pretty quickly. More specifically, I am
trying to have sheet #2 present informaiton in a gantt chart layout, the hope
being to provide users with a visual chart in sheet#2 tha tis clearner than
including such a chart in sheet#1 which has other functions and purposes.
I'd like folks to simply click on sheet two and see the gantt chrats and, if
desired, print that sheet for a hard copy reference. I'll be managing sheet
#1 and it would be nice to simply have updates "carry forward" into the other
sheet.
 
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
 
Back
Top