Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Columns to table | Excel Worksheet Functions | |||
Automating a series of dates for two columns? | Excel Worksheet Functions | |||
Automating charts with reference to new data and formulas | Excel Worksheet Functions | |||
Automating Linear Interpolation | Excel Discussion (Misc queries) | |||
allow user to disable annoying warning dialog on log charts | Charts and Charting in Excel |