Linking a whole worksheet to another in Excel

Question

I have two workbooks that have sheets that logically should look identical. I don't want to manually maintain their identical-ness, so I want to dynamically link them (just as you'd do between cells using formulas: "=A1"). I want to link the whole spreadsheet, not just a few cells.

Is there a way to do this without making 238427398729 formulas, thus crashing my machine? Is there a way to say "=sheet2" as a formula defining the content of a whole sheet?

Answer 1

You may be able to use the "Get External Data" feature (depending on the layout of your source sheet)

Steps to create in Excel 2010 (other versions menus are a little diferent)

1. Data tab
2. Get External data / From other Sources
3. From Microsoft Query
4. New data Source
5. Enter a name
6. Microsoft Excel Driver
7. Connect and select source workbook
8. Select required Sheet
9. OK, to open Query
10. Drag the * onto the grid
11. File / Return data to Excel
12. Select Destination

Resulting linked query can be manually and / or periodically refreshed

Answer 2

To dynamically link an entire sheet - manually:

Create a new sheet, in this case Sheet5 will duplicate Sheet3

in the A1 cell of the new sheet reference the source sheet =Sheet3!A1

Then select cell A1 in the target sheet (Sheet5 in my case) and use the lower right handle in the cell to drag it over to width of cells you need. In my case it was over to the "I" column.

Next select the cells in the 1st row of the target sheet that you want to use, and again use the handle in the lower right to drag down to as many rows as you need, in my case down to row 60.

Then go back to the source sheet and select the entire sheet (click the square between the row numbers and the column letters) and then click the Format Painter and switch to the target sheet and apply it to the whole sheet (click the square between the row numbers and the column letters).

You may find 0 in some cells, so you will have to manually clear content in those cells. You may also have to change the width of cell columns to match the original. Still, it is a pretty good duplicate that remains dynamically linked.

In my case I wanted the same exact data but wanted to have some columns invisible for print purposes. So for me, I just changed the font for the columns I did not want to have visible when printing to white. Thus I have a worksheet for preparing data, and a worksheet for printing.