Linking a whole worksheet to another in Excel

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


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.