ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Links and Linking in Excel (https://www.excelbanter.com/links-linking-excel/)
-   -   Link All cells of Sheet1 to All Cells of Sheet2 (https://www.excelbanter.com/links-linking-excel/252904-link-all-cells-sheet1-all-cells-sheet2.html)

Irshad Alam January 10th 10 06:21 AM

Link All cells of Sheet1 to All Cells of Sheet2
 
I am using Excel 2000

I have data on sheet1, more than 300 rows and about 9 columns.

As per requirement that I want link the sheet1 all the cells to sheet2 all
the cells, because on the sheet2, the autofilter option will be used by user
to view sorted data.

I tried to copy all cell and paste link, but it produces error. I know to
link sheet1 cells indiviually to sheet2 cells, but when the rows are
added/more data posted, it does will not update the another sheet.

Please advice me a solution, so that I can link one sheet to another sheet
on complete basis, so that if the data changes, it will update another sheet.
Or if row/ column added, it should automatically add on the other sheet also.

Thanking you all in advance.

Regards
Irshad




Bill Manville January 10th 10 08:37 AM

Link All cells of Sheet1 to All Cells of Sheet2
 
In my opinion there is no good way to achieve what you are asking.

I suggest you consider the following options:
- 1) don't keep a separate sheet showing all the data; just remove the
filter from the filtered sheet when you want to see all the data
or 2) use a macro to copy Sheet2's contents to Sheet1 when you activate
Sheet1 or when you change Sheet2, whichever is the less frequent
occurrence
or 3) [which I don't like] use a formula on Sheet1:
=IF(ISBLANK(Sheet2!A1),"",Sheet2!A1)
copied across as many columns as will be needed and down as many rows
as will be needed.



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



All times are GMT +1. The time now is 12:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
ExcelBanter.com